[00:07] you make, every product you order, and every report a business creates, all of it depends on data. But here is the real question. Where is all the data stored? How companies retrieve exactly the right record from the millions of rows and how
[00:22] data analysts, developers, and database administrators manage information without confusion? And that's where the SQL comes in. According to the US Bureau of Labor Statistics, database administrators and architects continue
[00:34] to remain important for organizations that need to store, secure, organize, and manage large volumes of data. And as companies continue to use data for decision-m, reporting, applications, and AI systems, SQL remains one of the most
[00:48] essential skills for anyone entering tech analytics or back-end development. With that said guys, I welcome you all to this session on SQL certification where we are going to master databases, query and real world data management.
[01:01] Now before we begin our session, just a quick info guys. Simon has got professional certificate program in data analytics, generative AI and adaptive systems in collaboration with IHFC and TIH of IIT Delhi. This program actually
[01:14] helps you master AI powered analytics using Excel, SQL, Python, R, PowerBI and Azure along with hands-on projects and real world case studies. You're going to gain 180 plus hours of learning, 40 plus exercises, 15 plus industry projects and
[01:28] capstone projects, plus access to tools like Tableau, PowerBI, AWS, Azure and GNI tools like chat, GPT and clot. Learners will also receive a certificate from IHFC, TIH of IIT Delhi along with job assist plus for réumé support and
[01:43] mock interviews and career guidance. So guys, hurry up now and join the course. The course link is mentioned in the description box. Now before we move quiz to test your knowledge. And the question is what does SQL stands for?
[01:56] And your options are simple query logic, structured query language, system quality language or software query list. Please mention your answers in the >> So guys here today's agenda is this one there. So already some notes is there
[02:10] now but anyhow I'll explain it clearly. Don't worry about that. I'm not going to read anything here that. So okay while even if I'm having the sentences everything I'll explain it right now. Okay. So introduction to database is
[02:23] first thing. So some basics of actually we have to know what is a database. Uh why we go for the database these all things. Then afterwards we'll have some concepts of MySQL. What is MySQL? The environment of MySQL. How to install
[02:38] MySQL. These all things we'll come to know here. Okay. That is yeah guys see there the first of all the thing is right now here so what is a data what is a process what
[02:54] so what is a data what is a process what is an information what is data what is process and what is information first of all we have to know about these three points here what is data so what is
[03:06] points here what is data so what is process and what is an information that see why because The guys that here if you are going to start any task if you are going to start any task or something that so these three steps are very
[03:21] important and it is mandatory by knowing or unknowing it doesn't matter that whether you know or whether not known that every process will go for with these three steps only data process information.
[03:35] So what is data here? Let's see that a data nothing but just it's a collection of raw material or collection of facts and figures something we say that so which means that there is no meaning for the data
[03:50] raw facts and figures raw material is not having any meaning for that with the help of that raw facts with the help of that raw facts or raw material we have
[04:02] to go for some work on it we have to go for some work on it here. That work we call it as a process. The work we call it as a process that after
[04:15] completion of process after completion of process again whatever the data we get again whatever the data we get uh that will be called as information that will be called as a information that so
[04:31] will be called as a information that so here the best in a generic way in a in a generic a generalized a simple example is right now there constructing a house Constructing a house becomes we can call it as an hour these three things. What
[04:45] is that? What is the data for constructing house? The material what constructing house? The material what you gather. So to construct a house what material we gather for that? First of all there should be a place then now
[04:59] sand, cement, bricks and all whatever the material we gather to construct a walls that everything we can call it as a okay like what you can say raw material or also even a gopher iron steel also we gather that is by mixing
[05:14] of these all things we construct walls. The constructing walls is not going to be taken randomly that there should be a process for that. That means there is a plan to construct a house. According to that plan the walls will be constructed
[05:28] or also the pillars will be uh like constructed and all that everything we'll call it as a process. That everything we'll call it as a processing? By using raw material that nothing but a data. So once that process
[05:43] nothing but a data. So once that process is completed that means so a beautiful house will be constructed. So that constructed house is called as an information that constructed house is called as information. So this is the
[05:56] generalized example. Okay. And when you coming to the IT sector when you are coming to the IT sector like this like guys you take in any industry the same thing will happen. You go for working with the speaking language working with
[06:13] the speaking language. speaking language it might be an English or Hindi or any native languages local languages Telu or Hindi I'm a Telugu person actually okay I'm a Hyderabadi Telu person so that uh Hindi Telu English any like Canada Tamil
[06:29] like whatever the speaking languages are there first what you have to learn in the sense now you have to learn some alphabets of that speaking language so that alphabets everything called as a here data you can call
[06:44] So after learning of alphabets what we learn in the sense of collection of learn in the sense of collection of vocabulary we learn some words. So after learning of words now we have some set of words is there. Now we have
[06:56] to construct a sentences. So if you want to construct a sentences using that words. So definitely you must aware of grammar. Without grammar you can't construct a sentence in any speaking language
[07:09] whether it might be English or Hindi or Telugu or Tamil or something blah blah. Once the constructor you are able to uh you are you constructed a sentence that's called as a a meaningful information is there within that the
[07:21] processor data same thing whenever you come to the IT same thing whenever you come to the IT sector see the computer performs any task for us but the computer cannot performs any task for us without telling
[07:36] to the computer by the human being if you're not saying anything to the you're not saying anything to the computer won't perform anything suppose I switch on the computer and I got a desktop then if I sit I ideal can
[07:49] will it be done anything for us what what you are expecting from the computer no it's not possible computer is a very clever machine but computer is a very clever machine but when when you instruct to the computer
[08:04] then computer can perform that but computer cannot be performed anything by its own by its own That remember that point. What'sa we're writing something on screen.
[08:19] we're writing something on screen. You want to say anything? that guys. Until and unless if you have a query, please send the query in a chat
[08:34] a query, please send the query in a chat box please guys. Yeah. Thank you. Thank you. So that is what here now. So we whenever
[08:46] you want to perform anything in the computer first of all you have to say to the computer please perform this task please perform this task. That is one thing. So that's called giving an input that's called here. Now we have to
[08:59] provide an input and to the computer to that input we have to say that you process that input in a proper way that what we have given instruction the computer will give an output the computer will give an output. So in a
[09:13] generalized way data process information that when it coming to the IT sector that so there is there should be some input should be given to the computer to the instruction what we have given and based on that output will be given
[09:29] to us here okay this is what just going to be taken okay but uh in a computer sector that means in IT sector there are two parts of working
[09:43] languages one is what here now learning programming languages guys I'm asking a simple question what we do with the help of learning programming languages I'm asking
[09:57] question who knows the programming language knowledge what can we do after learning the programming languages suppose someone is saying that sir I know Python so what can we do with the help of Python after
[10:09] completion of learning process Not only Python we have a so many programming languages are there in IT sector like Java, Python.net like so much of programming languages are there. What can we give that one? Yeah, current
[10:23] Jwani giving set of instruction to the computer. Okay, we have given set of instruction to the computer current but with instructions what is the output you get? So giving instruction to the computer is fine that okay interacting
[10:39] with the computer is so the current answer and savvi answer is also correct but the thing is see uh you think in a high level way both of you I'm not wrong it's correct answer perfectly correct that but in a high level way once you
[10:56] learn a programming language once you learn programming language then what do you do in the sense now we have to develop software We have to develop the software with the help of programming languages. We give
[11:10] instruction to the computer. You do this, you do this like that. Okay, that is fine. But doing that this task and that task and all that here. Now making that task and all that here. Now making softares. Making software that is sur is
[11:22] also saying that one like communicating with the computer it helps us to develop the software and solve the problems. Yes, that's fine. Yeah, that is what so learning programming languages. So learning programming languages in the
[11:35] sense right nowadays whatever the softwares you are using in a real world than in a society and by the different different organizations suppose if you go to the banker you will see completely computerized banking software
[11:51] computerized banking the every employees working banking relevant software if you go to the hotel supermarkets or some other any shopping malls you are getting computerized bills that means the bills generating by the computer and print
[12:05] will be given to us. Let's see that is anywhere you go and if you go for any other organization there they are using the relevant softwares. So how those organizations? Is it ready made available in the market to go and
[12:20] their their computers in the sense? No, it's not possible like that. Then what will happen in the sense those all softwares has to be developed by using
[12:32] some X programming languages. It might be a Java, it might be a net, it might be a Python or any other programming languages. That is one thing. So that uh learning programming languages used to develop applications
[12:48] or softwares applications or whatma that is softares that that is one thing but one thing guys so applications suppose a banking people are working with banking software but
[13:05] this banking software deals with the customer's information customer's data customer's information customer's data so where the data can stored securely and permanently. So that is what here now. So here we
[13:20] So that is what here now. So here we have to go for that database systems are have to go for that database systems are used to store data. What method is securely? What method is securely and permanently?
[13:35] Okay. Permanently that is. So these are the two things he's going to participate the two things he's going to participate in our daily IT sector activities. Okay. So programming languages requirement is one and database systems
[13:50] requirement is one and database systems are another one here. Both are interlin. Every software works with one database system. Every software works with the database system but database systems are
[14:02] invisible. Database systems are invisible. Here in our just listen guys see in this session we are going to be we are related to the database systems
[14:14] we don't want to discuss anything about the programming languages how the database systems are existed what are their architectures and how data can be stored securely in database systems and how to use that data from the database
[14:30] systems in a different ways that everything we are going to discuss in everything we are going to discuss in our sessions Guys, I hope that so my way of teaching phase is understanding by everyone right
[14:44] because it's the first session that's why I'm asking you that guys is everyone why I'm asking you that guys is everyone able to follow how I'm explaining that
[14:58] if anything there guys please post it on chat box okay queries chat box okay queries yes fine here. So now what is the point we concluded here? So there are uh like a
[15:13] two ways know dealing with IT sector. One is about the programming languages one is database systems where we store the data where we store the data. Here
[15:26] let's see that I have given a simple question and answer also regarding programming languages. What is a program? Now you guys has told that like giving set of instruction to the computer communicating with the computer
[15:38] that everything go for what is a program. So programming is a set of instructions communicating to a computer how to perform a specific task that how to perform a specific task that simply you can call as a a program
[15:52] but we can call it as a program that here. Okay, that's about irrelevant as concerned about the programming languages. So then [clears throat] one more question guys here. So why should we store some part of data in the form
[16:09] we store some part of data in the form of tables? Why should we write a timet in the form of table format only? Why can't we write a timet in the form of sentence suppose in a schooling uh in a school or college or in educational
[16:21] school or college or in educational systems that uh so first hour some language so second Monday first hour language second hour English third hour mathematics or something that so why should we write in a form of time table
[16:34] that yes easy to understand to get the data in a structured manner fine that is to store data in a proper success so that we can access easily when it is required. Yes, Karan. Yes, all answers
[16:49] are correct. That is because sometimes some data not every data some data we need to present in the form of table for easy accessibility. If it write if it is written in the form of sentence if it is written in the form of sentence
[17:04] obviously what happen you know until and unless you read that complete sentence what is the information is there that we can't understand. But uh when it coming to the table format by seeing its rows and columns by seeing what mats it's
[17:18] rows and columns you can easily understand what is the task has to be done or what is the action is there within that fine and see there why this table format I took here in the sense so our database system stores in the form
[17:34] of tables our database system stores in the form our database system stores in the form of data stores in the form of table okay that is one thing okay here so that's what just see there guys here now the
[17:47] same thing I have gone for that for easy representation accessing purpose table is used so database system uses the table format to store data so that now
[17:59] the continuation to this one data process information the next one we go for table so then you may come to know here s to store data data in the form of table. To store data in the form of table, we have
[18:14] some software tools are there. We have some software tools guys. Anyone knows that where we can store data in the form of table? Any software tools? Do you know that guys? Guys, do you know
[18:31] data in the form of table? Except databases. Except databases. You are all giving data as MySQL oracle something not that I'm not expecting that one here except databases. Yes. Naven yeses that is yes exactly. Yes. MS
[18:49] Naven yeses that is yes exactly. Yes. MS access Excel and all is there. Then here question arises what is that? You know anyhow we have some uh software tools where we store data in the form of table where we store in the form of table.
[19:04] Some tools are there here. Excel is saying you are saying Excel MS access fine exactly that only especially these two tools are the major tools that okay two tools are the major tools that okay but then why again we are going for
[19:17] database systems just now I told you that okay now so just now I told you that okay now so this is what okay so here why because in the sense of course so the current is saying something that's correct let's
[19:29] see that is so because of one thing that guys here so there are three reasons here. Now the very first one is file processing very first one is file processing system. So I think uh see there this we
[19:43] will take it file processing system will have the limitations will have the limitations. So to avoid that limitations so we should go for that uh databases. We should go for that databases here. See then why database
[19:59] databases here. See then why database system this is so to overcome limitations of file processing system. So already you you guys has been given the answer. The same points see there here. What are the limitations of file
[20:12] processing system? First of all limited data stories. A file process a file an Excel file or MS access file whatever it is there that Excel file MS access file always having some limitation to store the data. But
[20:29] you think in a big manner. Think in a big manner in the sense of for example you take a bank you take a banking system suppose a bank having
[20:41] suppose you take a bank like ICIA bank is one bank [clears throat] this ICI bank having one lakh customer just assume that having one lakh customers so one lakh customers information has to store in a bank okay it has been stored
[20:57] these one lakh customers guys try to understand these one lakh customers is performing at least one transaction per day. At least one transaction per
[21:09] per day. At least one transaction per day. That means so the day one lakh transactions 30 days 30 lakhs transactions. 365 days 365 lakhs transactions. See there this entire data has to be stored. Is it possible to
[21:24] store this entire data in a in in a file kind of Excel or MS access? Is it allows kind of Excel or MS access? Is it allows in the sense? No, it is not possible. in the sense? No, it is not possible. It is not possible here. So that is one
[21:39] limitation of the file processing. The second one is here now security. File processing is not files are not having secured systems. So I can protect that file with the help of password in the sense but of course
[21:52] you can protect the data with the passwords. Okay, that is one mode. But the even the password in the sense you cannot open the file but I can delete that file. I can delete that file. I can rename
[22:05] that file. That kind of activities you cannot uh stop that. So that no security that is one thing So that no security that is one thing then uh data access is very difficult
[22:19] you like I say a bank one lakh customer's information is there that one from that one lakh customer's information one particular customer information I wanted to access based on account number or name or blah blah that
[22:32] is is it possible to get that using excel sheet if of course it is possible I'm not saying it's not possible it is possible definitely it is possible but Takes time. Takes time that
[22:47] only getting an account details. Suppose if a person goes a particular candidate goes who is having account and he will ask one statement of their account. Sir get me that last 3 months statement of my account. Then he has to gather the
[23:01] information from different sheets and all that is very difficult. It becomes very very difficult that and here like Swagatika is saying that one duplicate data of course data might be going for duplications here. So these are the
[23:18] major three reasons to avoid the file processing systems in organizational data. Organizational data we are talking about organizational data. Listen about organizational data. Listen carefully that organizational data.
[23:31] Okay. So that these all will overcome the database systems. These all overcomes what that is database systems that here now. Okay. See, so these are the limitations here. Now from that limitations, the database
[23:47] systems will overcome that. See, so what does that say? A database is a better for managing large data sets, complex data relationships or when multiple users need to access data, access and update data simultaneous.
[24:03] So this is the answer for this one. DB will provide full security of data, allow stores large amount of data, will provide a query language to access or provide a query language to access or manipulate data.
[24:18] So these are the three reasons for that we should use database systems for an organizational data. So don't think that Excel is not useful. Of course, Excel is not useful for the organizational data. Excel is useful for
[24:33] personal data management. For me, for example, myself is one person. I wanted to go for my all daily activities. So and I wanted to manage in in Excel sheet. It is perfectly suitable for me there.
[24:47] only one for me or my family like for you for your family or else a small scale organizations any small scale organizations is the Excel and MS access is useful perfectly useful but large scale data storage for
[25:04] organizations when that is I have given you only one lakh customer for IC bank but really is it is the bank having one lakh customers no some cr of customers is there or else you take another industry Take a social media accounts
[25:18] Instagram, Facebook is that Instagram details, Facebook account holders having the data in Excel way. So not at all possible that like education system, universities, school is limited but board, state board,
[25:36] central board, universities. So how much of data? See this is completely about of data? See this is completely about data guys. We are working on data completely. We are dealing with data that large amount of data that is what
[25:49] we should do. Yes. Now come down to the what is a database. Now come down to the what is a database here. Yes. Let's see. No. So in
[26:02] a general way what is a database in the sense of we can say that like this it is a collection of raw material or facts and figures organized in a structural format. What is the structural format here? Now table of an entity
[26:20] of an entity or else simply you can call it as it is in a collection of it as it is in a collection of interrelated tables of an entity. It is a collection of intered tables of an entity. So like that uh we can say that
[26:34] entity. So like that uh we can say that is so the entity might be any school or any organization any form that a banking system school college or any insurance department a medical department medical department hospitals or else any like
[26:49] supermarket one medical stores whatever it is you can take that is that is called an entity here. So for that how the data will be stored. See that guys for that to I have taken here now databases student sorry
[27:06] databases school database I took here. Now I simply can and simply and roughly I just categorized like this basically school having students employees and
[27:19] transportation okay transportation library these all different categories of school will be there. So when it come to the students what kind of information we store in the form of tables or something that is so now here these all
[27:35] you can consider guys here now these all are tables personal info class info are tables personal info class info attendance info exams info marks info fees info games and sports info like this is there
[27:48] if it is personal info then what will be the details we gather for that see there now registration numbers Student name, father name, gender, age, class admin.
[28:00] That means admission has been taken for the class location and what is the role student. Registration number is different from role number. Okay, that is one thing. Then when when he's joined to the class that
[28:16] then class details should come here. So role number, student name, class, which section he has been joined, attendance info, role number, student name, section, days working, days present and in the month of that month and year
[28:32] what is month and year you can say that is like you can have examination info, is like you can have examination info, marks info, fees info, games and sports info like that relevantly student prospection and the same thing like uh
[28:48] we can Go for employees prospection, biodata info of the employees, job info of the employees, salary info of the employees, salary info in the sense. So if you observe that as you know that your pay slips there is lot of
[29:03] information present on the paylip, basic pay, net pay, allowances, deductions, okay like loans or whatever it is there that is called salaries info, paylip part. Then when it come to the library that books info is there members info
[29:18] issued books info like this. So now I can call it these all tables. I So now I can call it these all tables. I can call it these all tables comes under one particular school. So school database I haven't written for all
[29:33] database I haven't written for all tables call that attributes but one thing we so so this is what just we have to take down here. So what can we do it mad is suppose if a table is there. So table is
[29:48] suppose if a table is there. So table is a collection of rows and columns. Collection of rows and columns that this we can also call it as
[30:08] right svi? Yeah, data set in the sense now. So you can simply call it as a a collection of tables is called a data set. Okay. Now tables is called a data set. Okay. Now database is also called as a data set.
[30:23] Database is also called it as a data set. Here data set one particular table you can call that one. So you are saying that what is an entity? So yes, let's
[30:35] see the so whenever you wanted to have that that banana
[30:48] say that is welcoming questions here yeah see there as I told that one see an entity a simple definition part you can take down that here
[31:01] take down that here yes you see this one here.
[31:17] simply you can say the real world object that so as I told you that an entity nothing but a firm an organization any school or college or something that is okay here. So in a simple words they are saying a place is an entity, a person is
[31:31] an entity or a concept that is uniquely identifiable about which data is stored. Okay represented in the form of see represented as tables or rows in a what
[31:46] we can call that database entities are fundamental elements of entity relationship model the characterized attributes. Same thing I about to tell attributes. Same thing I about to tell here that table see that. So here now so
[32:00] see that guys one thing if I'm saying a person you tell me that what are the attributes of a person having general in general you and me personally sense human being you and me are the persons that now tell me every human being
[32:14] that now tell me every human being having what are the attributes guys please let me know that yeah Karan you and Svi tell me that what are the attributes yeah to identify the name to identify the person name is
[32:30] the name to identify the person name is there one gender kind of age and if it's a person educational qualifications living place living place and family details these all comes under a person details that in India if you pick up an
[32:45] details that in India if you pick up an other card of a person other card of a will come professionally or personally is it right
[32:57] professional or personally that everything. Okay. So that is that person is uniquely identifying in India with the help of other card. Every Indian citizen is identifying with another card uniquely. Okay. So whatever the
[33:09] attributes you have given that one the same thing has been explained here. See same thing has been explained here. See that entities are fundamental elements. So whenever you come down to the database here you see this one. Whenever
[33:21] you come to the database, so I took that school database school database in a school database can here now. So a database is also you can call it as an entities that is anything that. So now
[33:34] see there we are having lot of different tables has been identified here. Now every table has got some attributes that is the attributes I have given here. So collection of attributes we'll call it as a one record. See here that is a
[33:51] so that is what I told you here a table is a collection of rows and columns okay so here rows can be called it as a here record also what that is record columns is call as a
[34:06] here attributes or properties no don't confuse that is okay that is one thing collection of attributes or collection of columns or
[34:18] collection of property properties will become a row or a record. a row or record we can say that is for example let's say that here
[34:34] guys so now let's see yes suppose I'm taking now role number is one column then student name is another column gender is another column age is
[34:50] another column location is another column like this V. So now here uh so name of table you can say that let's say that uh uh student
[35:03] so this is one thing now here I'm giving some values that is here now okay gender some values that is here now okay gender male is some 23 location something like
[35:22] these all call it as what method what can we Call it these all here. We can can we Call it these all here. We can call it that uh attributes. We can call call it that uh attributes. We can call that is
[35:38] that attributes. Okay. That is one thing.
[35:51] Okay. Let it be. You can it is not allowing. Okay. No worries. These all call it as what in the sense mother attributes we can call that and you see this one these all are what in the sense now these are actually attribute values
[36:04] now these are actually attribute values those all call it as here record what those all call it as here record what method is record you can say that is this everything will be considered as one record or a row this is everything
[36:20] okay so that is one thing so here a role number is an attribute ute and its value number is an attribute ute and its value is 1. Student name is an attribute and its value is ken. Okay. So, gender is an attribute and sorry, yeah, gender is
[36:35] male is its value. So, like that you will be getting here that is record or will be getting here that is record or rows.
[37:19] more also we can go for that here uh this is called here record we can say this is called here record we can say that is yeah this is one entity that completely record one record or else we can also going for this is a row you can
[37:35] can also going for this is a row you can call that one what mother is row also you can call it that is like you have so much of student details we entered here so much of student details We enter like this there.
[37:51] We enter like this there. Okay. Like what we can call that is so like this we can go for it in this way that yes. So like you can add it
[38:03] this. So I hope everyone got it this one the differences between attributes and rows that so here one record of the student is an entity in this example. Yes.
[38:17] That's fine. Yeah. So guys, please let me know. So the differences between so what is an entity? What are the attributes? What are the records? I hope that everyone understood here.
[38:30] Guys, please give your answers. Please say yes or no. Even if you have any queries, please post it on the chat box. Guys,
[38:45] session very first time so I'm expecting an more answers from uh here now please an more answers from uh here now please give your responses guys
[39:06] But we you guys are having almost 30 candidates in the session.
[39:20] this chat box. Is my session is going somewhat fast or slow or in phase is somewhat fast or slow or in phase is okay or something else.
[39:40] that we are directly creating a what we can call that uh so this kind of data table and all that is but uh we have to go for database systems. Whenever we go
[39:52] for the database systems guys here like see that so we have right now DBMS see that so we have right now DBMS what is that DBMS is there see we know
[40:04] now what is the database we know now what is the database and different an example and its attributes blah blah something is there but how the databases can be created and how the databases can be worked and all that is yes there
[40:20] should be some software tools. There should be some software tools are there. Okay, within that software tools the very first one and very beginning level is right now there what you see in the sense DBMS is there guys listen there
[40:37] this is what just we are having DBMS. So what is a DBMS here? A DBMS, a database management system. We can call that database management system is a software
[40:49] that manages databases acting as an interface for users and acting as an interface for users and applications to create, store, organize, retrieve, update and secure data efficiently and consistently
[41:06] handling large volumes and multiple users simultaneously. That is what a DBMS is. There simply one thing might here now that the technical definition has been given there. But in a simple words we can say that DBMS is the
[41:21] software tool for working with the databases. DBMS is the software tool for working with the databases. Within that working everything will come. What is that? So storing data, modifying data, removing
[41:38] data, retrieving data, lot of activities we do, lot of activities we do that is within that lot of activities. So more mainly four categories of actions are
[41:50] mainly four categories of actions are there. First one is creating, how to store that is one thing adding data you can say that one. Okay. The second one can say that one. Okay. The second one is very important one retrieval from
[42:04] huge amount of data. From huge amount of data whatever the data you need how to retrieve that data very quickly that is retrieve that data very quickly that is called reading reading retrieving data.
[42:17] The second one often we perform the third one often we perform that how to modify data if required for a student's data for example
[42:40] volume that is because myself I'm speaking somewhat loudly only how about speaking somewhat loudly only how about others Guys,
[42:59] speaking loudly only that but anyhow I'll increase and he said no I'll increase that. So now here so simply you can say that one now simply you can say what is the database in the sense method so it's a
[43:15] database in the sense method so it's a software tool it's a software tool to work with the databases within that working so like storing of data then what we can call retrieval of data modifying of data deleting of data the
[43:30] modifying of data deleting of data the blah blah will go but on that blah blah the main categories are here now Four, one is storing, adding data to the table databases. That is one thing. The second one which
[43:44] That is one thing. The second one which is very important when retrieving data from the large amount of data. Let's say that a banking system having one lakh customers from that one lakh customers one customer information how to get
[43:59] one customer information how to get quickly. This is what retrieval process some kind of technique that is one more. Then the third one is here. Now suppose if a situation comes to modify the data. Suppose in a bank a customer wants to
[44:13] wants to update their address or something else. That is one thing. Suppose a customer wants to withdraw their account from that bank then deleting of that customer details from that bank. Deleting details these things
[44:27] will happen here. So that simply you can call it as here code operations. [snorts] What is that here? Now code operations is there. So C stands for here create for storing. U stands for here now
[44:42] for storing. U stands for here now update. Then R stands for here now read or retrieval. Then D stands for here now delete. D stands for what? That is delete. The mainly we focus on this. These these are
[44:57] the operations that but these will be done in a different different ways. done in a different different ways. Okay. That is what okay. So now to work on these databases. So so there are some rules in DBMS 14 rules is there. DBMS
[45:13] consist of 14 rules to create and work with databases which has been defined by MF card. So that those rules are called as card rules. to discuss those rules guys theoretically practically only we
[45:29] discuss that is by taking one software tool okay that is okay that is one thing just I'm forever information I'm just giving that 14 rules will exist that okay so that is one thing so what are database software tools guys here now
[45:44] let's see have you heard about these names guys have you heard about these names dbase and fox Anyone have you heard about those names? H [clears throat]
[46:05] would be fine. Nice. Sounds good that. So if it is because you guys are saying no no no yes that is because whenever the database systems are evaluated in the market in the past that 1980s or some '7s or something the very first
[46:20] some '7s or something the very first database systems in the world is dbase then modified version of that database system is right now right there pro is there those are the very fundamental very initial days of database systems
[46:36] those two software tools are now also available but no one is using that is available but no one is using that is okay later on that we got Oracle SQL okay later on that we got Oracle SQL server MySQL postgrads SQL DB2 there are
[46:49] so much of database software tools are there okay that is so we are concerned with MySQL guys we are concerned with MySQL but before that guys listen we have a types
[47:04] of DBMSS is there see DBMS has been evaluated kind of in the late 1970s or ' evaluated kind of in the late 1970s or ' 80s that is exactly uh we don't need that exact year or something that is okay but right now we are in the 2026
[47:22] we are in 2026 all about 50 years lifespan is there again even if you go for 1980 so that is 20 years this is again 26 or 45 years so this lifespan is
[47:34] there for these things but within this 45 years there is lot of changes has came into the software industry so that the DBMS also has been now uh modified in a different prospections and got renamed in a different ways and added
[47:49] renamed in a different ways and added some additional features to this in that prospection. Okay, here first modification first recognized modification first recognized modification of the DBMS is RDBMS
[48:02] relational database management system you can say that what is meant by that relational database management system okay here so the relationship between tables relationship between tables for example
[48:17] you see this is the student table that would be fine here okay now I'm giving there here now marks table that okay in this marks table I'm giving uh
[48:30] what is that serial number is one column then exam is another column so then here now subject one marks then subject two marks then here total marks let's see
[48:42] that is whose marks it is I'm going to introduce here now a column called role number a column called role number that is and a column called role number that is and let me add two more students here Oh,
[49:16] number is one that then exam is for example let's say that quarterly some 45 and again 46 okay now 46 that so 91 is the total so
[49:28] okay now 46 that so 91 is the total so whose marks it is listen guys okay here the marks of the student is not going to be entered directly because already some students list is there so the role number should come here any one of these
[49:42] four that is my role that is my rule here. This marks for the uh what we can say that is 1,3 let's say that is.
[49:54] So then second one quarterly okay now like 60 here again 60 some 120 is there this is 1,1 and you see this one guys for example
[50:06] and you see this one guys for example let's say that here so I'm just giving that right now so in this way so 130 is the answer suppose if I give 109 is this role number is a valid is this
[50:22] role number is a valid here Guys, so please tell me is the role number is a valid? No. So here this column, this column should not accept the role number which is not present here
[50:38] since it is Excel. Okay, we are able to give. Okay, buth as for the database systems, it should not act because so these two tables are not having any relationship. But we have to maintain a relation between student
[50:54] maintain a relation between student table of role number column to marks table of role number column. Marks table of this role number column that is whenever we make a relationship between these two here. So here this only this
[51:09] value won't be allowed because whenever you are entering a value in this column that value will be referred into these four values. So when the when that value is present here then it allows here. In that way we
[51:23] wanted to make a relationship but uh in a DBMS systems listen carefully guys in a pure DBMS systems it is not possible. So that everything has
[51:35] is not possible. So that everything has to be taken care by has to be taken care to be taken care by has to be taken care by like user only that is user only. So when the DBMS rules 14 rules has been developed that relationship concept has
[51:50] not developed within this 14 rules that relationship concept is not there even if a situation comes that we have to maintain explicitly by the user itself. So under that these two softwares will work. DBase and Fox Pro will work. That
[52:06] work. DBase and Fox Pro will work. That is DBase and Fox Pro will work. But later on that that necessity has been recognized and the relational process the relationship process rules has been added to this DBMS. Then the name has
[52:22] added to this DBMS. Then the name has been given for that RDBMS. Only one feature I explained here in Excel. Okay. relational regarding feature that is the main feature and
[52:34] some other aspects also there so the name has been given to the RDBMS [clears throat] relational database management system in this relational database management system we got a concepts called primary key foreign key
[52:48] concept the foreign keys the concept has been introduced making relation between been introduced making relation between tables to avoid the inconsistent data or duplicate data or something Okay, that you will come to know while
[53:01] working on that. Okay, so that is the one here. Now RDBMS guys here now one here. Now RDBMS guys here now relational database management system
[53:16] swagatika so I'll provide it everything that is okay you will come to the sessions that no worries okay now just follow this one
[53:29] even if you want you can noted down the points okay no worries that okay but I'll give you everything from my side in a sessions that then one more is there guys here now like modern database management systems are there what is
[53:43] this modern database management system in the sense It is perfectly suitable for the present trend. Okay, present trend that is how to communicate multiple databases which is located in a different servers.
[53:59] how to communicate multiple databases which is located on a different servers for example let's say that one more example I'll give you here uh yeah you take a banking system of multinational one suppose I say a bank is having in
[54:15] multiple countries multiple countries that is so how abroad customers will communicate into Indian branches or else how Indian customers are communicate with abroad branches Suppose if a person has been taken
[54:30] account in abroad they will maintain their own database system separately in their country Indian branches. So Indian customers having their own database customers having their own database systems in Indian branches only that is
[54:43] but having accounted same bank so that might be have some transactions between one country to another country then how database systems will communicate for that one there is some kind of rules and rules has been developed which is called
[54:58] as like modern database management systems. So just mother but as of now we don't require that just we are having that MDBMS process then one more very that MDBMS process then one more very simple one that DBMS is there that is
[55:13] called here object relational database management system. So I hope that some of the people who are having programming knowledge like object- oriented programming structure oops concepts is there which is purely
[55:27] comes under the programming languages that programming language concepts that programming language concepts object to class something like we are having some kind of concepts are there. So within that object to class concepts
[55:41] has been implemented on DBMS which is called as object relational database management system. With that we got some concepts is there partitions clusters some kind of concepts has been there into our uh what we can call software
[55:56] into our uh what we can call software tools that is but one thing guys these tools that is but one thing guys these two are occasionally uses two are occasionally uses occasionally uses that is our running
[56:09] occasionally uses that is our running DDMS is right now there here RDBMS DDMS is right now there here RDBMS so in RDBMS only like MDBMS MS will come, war DBMS will come. That is because of that uniqueness of that
[56:22] feature. So that for that one we got a separate name for MDBMS. We got separate name for W DBMS. There is no any separate tools for MDBMS or W DBMS here. Now every tool comes under the RDBMS only like we have SQL server, you have
[56:38] Oracle, we have MySQL, Postgress SQL, DB2 and all that is we will work on those things only. Okay. when that situation so how to communicate multiple databases which is located in different servers then the like what the MDBMS
[56:54] will come that is the situations that is when you are you wanted to pass data between one object sorry one database another database in the form of object then RDBMS concepts will come that so that is what so that apart that
[57:11] everything going for RDBMS Everything going for here now RDBMS Everything going for here now RDBMS prospection only that is
[57:29] yeah so one thing one let's see okay
[57:42] very simple thing that this is one database database here. Now, okay, you take local itself now database of ICICI
[57:54] itself now database of ICICI bank. Okay, and here now that is all customers info. Okay, that is one thing. Okay. Now you
[58:06] Okay, that is one thing. Okay. Now you go here. Now one more. Uh Okay. Like what we can call in the sense SBI bank guys. So all customers
[58:25] SBI bank guys. So all customers info. What is this one? This is the SBI bank database. This is the ICA bank database. So now all customers are having their account details over there. Guys one point you see
[58:39] whenever you want to withdraw amount from ATMs by using our debit card are you going to withdraw the amount by going to same ATM center same bank ATM going to same ATM center same bank ATM center.
[59:05] irrespective of the bank we go to that ATM we insert the card and we withdraw ATM we insert the card and we withdraw the amount. Okay. But how for example now I have ICA bank account but I'm withdrawing amount from the SBI bank.
[59:21] withdrawing amount from the SBI bank. How SBI bank will come to know that okay he is the ICA bank customer and the transaction has to come from the ISA bank not from the SBA then he will come to how it cost here yes so here bankto
[59:39] bank communication will be there what that is bankto bank database communications will exist banktobank communication will though You
[59:51] are doing transaction from the SBI bank ATM. Okay. But your transaction will ATM. Okay. But your transaction will happen only on the ICA bank. Same vice versa. Same vice versa that is. So now here this bank should access. So I bank
[01:00:07] your account details to here database one database. This is different server. This is a different server. This is different server and this is a different server. Database servers there. So how to get this different
[01:00:21] database server uh one customer details into here? Yes, there should be some rules for that. That rules has been defined in MDBMS. Yeah, inter exchange between the banks. Exactly.
[01:00:34] There are some rules has been defined. So here look that is a high level I told you but in a database level so how to communicate one database to another database placed in different servers
[01:00:48] placed in different servers placed in different servers that so what are the rules we should follow in DBMS prospection in what that is DBMS prospection those rules all comes under the MDBMS
[01:01:05] rules all comes under the MDBMS I hope you Got my point right now?
[01:01:17] guys having any other queries guys. having any other queries guys. Anyone having any other queries
[01:01:35] it in this is what introduction to a simple introduction to databases there it comes right now guys. So what is a MySQL? Because we are concerned
[01:01:47] with the MySQL because we have so much of software tools are there. Verac SQL server, MySQL something that is here. What is a MySQL? something that is here. What is a MySQL? What is MySQL?
[01:02:01] Simple thing guys. So it's one of the database system. So that again no need to repeat everything. It is one of the RDBMS database tool. RDBMS database RDBMS database tool. RDBMS database software tool. You can write on that
[01:02:15] software tool. You can write on that software tool. You can say that is here. The current version of this one 8.0 is there. 8 is there. Now, and one thing guys you have to remember any database system you took either Oracle or SQL
[01:02:30] server or MySQL or anything that there are two parts. There are two parts is are two parts. There are two parts is there. One part is SQL part. One part is SQL part. Second part is programming that is a TSQL part. Names
[01:02:45] might be different in other software tools but there should be a programming also inside the databases. Okay, that is one thing you have to keep in mind. That is one thing you have to keep in mind that is so that SQL all the
[01:03:01] commands will be there TSQL transact SQL programming part will come. First we'll programming part will come. First we'll continue with this. SQL continue with this. SQL and one more thing that this SQL is a
[01:03:14] common to every software tools whether you go for veracular or SQL server you go for veracular or SQL server postgra SQL or DB or even you go from postgra SQL or DB or even you go from the DBS FoxPro also it is common
[01:03:28] might be having some little syntax differences between one software to another software the two 90% same syntax taxes only 10% cases will
[01:03:40] same syntax taxes only 10% cases will have the syntax difference that's it why you know that this is the reason you see this what is SQL links see it is the universal language for databases
[01:03:54] why it become a universal language for database systems in the sense of initially it was developed by anc company but they could not able to develop the environment for it so that uh that SQL was started selling to
[01:04:11] uh that SQL was started selling to whoever wants that. Okay. So that is but in the olden days where it was working in the sensor at command prompt in the sensor at command prompt per minutes at command prompt that okay
[01:04:25] in that way it goes there. So SQL was using at command prompt later do on developed go environments for different companies. So actually it was developed by ANC. It was developed by ANC but nowadays so it
[01:04:41] is using no but nowadays ANC's company not using the SQL because they have been sold to different first that has been sold to Oracle. Oracle has been take over the has been taken that SQL and further to they added
[01:04:56] environment they have added programming part and all and they released as a Oracle. Then they took that Microsoft people then it has been developed surrounded to that and developed one product called SQL server
[01:05:11] SQL server that later on that like MySQL postgq even for all suppose for example you have a thought sir I want to develop my own SQL yes you can develop get the like license from the ANC company about that SQL then
[01:05:27] you get your own environment and you add your additional features for And you can your additional features for And you can release yourself one SQL tool release yourself one SQL tool like that. So SQL is full of commands.
[01:05:41] like that. So SQL is full of commands. SQL is full of commands that okay how those commands will come and all will be coming into this one here.
[01:05:55] commands you have in SQL. Now we'll come to know Now we'll come to know the commands mark. So how the commands the commands mark. So how the commands will go for here?
[01:06:12] How the commands will go? So there are five categories of commands is there. Data definition language DDL commands are there. So these commands are used to define new structures of database as well as modify
[01:06:27] structures of database as well as modify and deletion delete structures. The commands are three create to alter drop. Just listen the words while explaining that what is the data definition, what is the data manipulation and all that.
[01:06:43] is the data manipulation and all that. Okay, that is then data manipulation language commands are there here. These commands are used to add new rows to the table as well as modify, delete rows from table. Insert, update, delete is
[01:06:59] there. Data retrieval prospection only one command is there. Select retrieving data. Then a transaction control language commands are there. Commit and roll back commands we have that. Okay, that is
[01:07:14] then data control language commands are there. So giving permissions, withdrawing permissions between the databases and all that. So grant and revoke will come there like we have totally five types of commands and
[01:07:29] commands are there here. Now 3 3 6 7 9 11 commands are there. If you talk about these 11 commands our SQL will finish. Okay. But these 11 commands has got a
[01:07:41] different topics when it coming inside of that MySQL. So topic wise the commands has to be discussed. Topic wise commands has to be discussed here that is okay. So then we'll come to know
[01:07:56] first we have to start from here. Okay that is but before that we have to Okay that is but before that we have to see the environment of MySQL part. Okay, this is the commands part we entered. But we go here now environment and all
[01:08:13] that is. So the very first query what should be right here and how that is in the sense first of all we have to create one database. So from here you will be getting that uh
[01:08:27] So from here you will be getting that uh see there here now what is a database? What is a database? See then as for MySQL collection of objects as an an object is a data item. An object is data item in database. What
[01:08:44] are that data items in the sense? Table, views, procedures, functions, indexes, triggers. These all will come into that. These are the discussible topics we are going to discuss in upcoming sessions that. So that first we have to create
[01:09:00] that. So that first we have to create here database. So how to create a database? How to create a database? This is the command we have to go for that from a DDL create command has to use create database. Database name create
[01:09:16] whatma that is database database name any database. So just we can take down here. Let's see that guys how I'm writing here. Guys please follow that. So I'm taking now create [clears throat] space database space F 2026
[01:09:36] space database space F 2026 DB I'm writing now and put semicolon at last okay every query ends with semicolon here and one more thing guys here you have to select that command
[01:09:50] have to select that command that entire command has to be selected once it is selected you go This button is there. Flash symbol button is there. Okay, see there now the first button. The three buttons is there. Folder, save
[01:10:04] and third button. This button is there. This button you just click on it. So once you click on that button on the bottom side, you'll get one message and green color tick mark that means that database has been created that command
[01:10:19] database has been created that command has been executed successfully. Okay, that is the confirmation. We got it here. Now where can I see that database in the sense guys? So I hope everyone is seeing here administration
[01:10:34] in the right left side panel. Administration you are seeing a everyone. Okay. Everyone seeing this administration. Beside of that only one arrow mark will be there. See there
[01:10:49] arrow mark is there. Here one arrow mark. You click on this arrow mark. So that what will happen. You see that you will be shifting into the schema section. You will be shifting into the schema
[01:11:03] section. Left side arrow mark is there. If you click that administration, if you If you click that administration, if you go to here, this one schemas. Once you get this schemas, here you have some schemas. SIS is there. below of the SIS.
[01:11:19] schemas. SIS is there. below of the SIS. Below of the SIS, right click on it, Below of the SIS, right click on it, right click on it. Go for refresh. Whenever you go for refresh, the database what you created will make
[01:11:32] visible here. We'll make visible. Do it that guys. We'll make visible. Do it that guys. Right now, do it that guys. So, first what should we do? Ma you go for type the command create database. Database
[01:11:47] name any name you can give. I have given Feb 2026 DB. You give any name test DB, Feb 2026 DB. You give any name test DB, demo DB, okay, self DB, anything that you can give it. I have given monthwise.
[01:12:02] So after writing that you have to select that entire command and go for clicking on this button guys you see there. So you see my mouse finder where I'm clicking. I already clicked this. Click on that. You get confirmation
[01:12:18] once it is done here actually you guys are having like administration the right arrow button is there you click on this right arrow button you'll get like this
[01:12:30] right arrow button you'll get like this and here sis database will be there so below of that right click on it and refresh the database what you created that will disable here guys please confirm that how many of you succeeded
[01:12:47] Please confirm that how many of you succeeded that is everyone visible that is yes
[01:13:02] chendra praash what happened to you just let me know is what happened to you just let me know is are you facing any issue
[01:13:21] patients. So once we settled on this environment then we will keep on uh working on the commands. Yeah, we already you already did that.
[01:13:33] already you already did that. Okay. So, can you uh decrease the screen zooming? Can you decrease the screen zooming? Screen zoom. No. No. Maximize it first. Maximize it.
[01:13:47] No. No. Maximize it first. Maximize it. Maximize. it's not getting maximize. So, go for control minus on your keyboard. Control minus on your keyboard. Hold on
[01:14:02] button. control-y button on your keyboard. You do it that control minus
[01:14:19] the control key and click on the hyphen button hyphen button so that your screen zooming will decrease. Uh okay wait okay okay wait don't do
[01:14:34] anything just wait now there you are seeing administration no in the left seeing administration no in the left side uh then click on that arrow button uh right click on white area white area not that one in the below sis
[01:14:50] white area not that one in the below sis below below of the sis yeah yeah refresh yes you got the that is keep like this on your screen okay stop sharing now you on your screen okay stop sharing now you got
[01:15:09] create one more database you create one more database now I am also doing that guys create database test DB like this you
[01:15:21] create one more database and executed that that create one more database and execute it.
[01:15:36] database is the command or fabric. It's not an attribute here. Database name one I shown you know that see there it is a database name see
[01:15:48] create database the database should be identified with one name database name. Okay, that is the name of database should be given.
[01:16:00] should be given. I hope you got it right now. refresh once again. So two databases has to show like this on your systems,
[01:16:14] right? Yeah. Now try to understand guys. I created two databases. You also created two databases here. Now you have to decide that on which database you have to work.
[01:16:30] Now you have to decide that which database you have to work here. You have to choose that is so how to choose that on which database you have to work in the sales guys one more command I'm introducing right now here use
[01:16:46] see how to change from one database to another database use DB name use DB name another database use DB name use DB name will be there here that is one thing so
[01:16:58] I'm doing like this what is that so suppose if I want to work with the first suppose if I want to work with the first one use FB 2026 DB should go and select that command.
[01:17:13] Select that command and uh click on execute button. So that you see there that database has been highlighted here and has been released like this tables,
[01:17:25] and has been released like this tables, views, stored procedures and functions. No sir, I don't want work with that. I want test DB. So once again use uh test DB. Okay. Then click on this one. See test
[01:17:41] DB has been released. Now see two has been released but see the highlighted part. Test DB is in highlighted part. That means you are working with test DB
[01:17:53] which means that now if you create any tables, views, procedures or blah blah whatever it is that everything will be stored in this test DB database. No sir, I want to work with the FB DB. Okay, select that and execute here. This
[01:18:10] will be highlighted. Now you create any tables, views, indexes, procedures or something. everything will go on stored on into in this database. on into in this database. So now my point is here use command is
[01:18:24] So now my point is here use command is meant for uh what we can changing between one database to another database. Databases are fixed once the database has been created that is permanent.
[01:18:38] has been created that is permanent. That is permanent here. But so if you are having multiple even one database also if you have that one first the every day when you enter into MySQL first you have to use use command use
[01:18:51] the database which database you want to work so then within that tables views and all everything we have to work on it. So everyone is seeing the same results now. Use command everyone seeing the same results guys.
[01:19:23] Yes guys fine it is. So now what next? So what is the next thing we have to do it here? What next? In the sense obviously in the What next? In the sense obviously in the database actual data will be stored.
[01:19:38] Listen guys here now actual actual data will be stored in tables. Actual data will be stored in tables that. So now here we have to know how to
[01:19:51] create a table. We have to know here how to create a table. Let's see that how the table will be created. Let's have now some explanation of the create table now some explanation of the create table command. Okay, let's see there
[01:20:06] guys. Right now there see there next point here now how to create a table. So for a create a table we have to go for that create a table. Table name is
[01:20:19] what here? Now say any table name should be given. Sorry create table. create command. So it's a database. So that to create a database we said and here a
[01:20:31] create a database we said and here a table. Create table. Then any table name you have to give. Then you have to open the bracket round bracket has to open. Then within the table the columns list has to mentioned.
[01:20:48] See guys when coming to this Excel sheet the today morning see role number is one column name, student name is one column name, gender is one column name, age is one column name, location is one column name like this. So we have now some
[01:21:05] columns but whenever you come down to this column all the numbers we are entering. Whenever you go for this one names we entering. Whenever we go for gender again text has been entered is this is
[01:21:19] again text has been entered is this is numbers again location text like this if you have a birth date values okay know birth time time values something we'll have like this. So when it coming to here see there first you need to mention
[01:21:34] here see there first you need to mention the column name and it's a data type has to mentioned that means what type of data you are entering into that I'll tell you what are the data types having in MySQL
[01:21:48] then one more is there here constraints guys remember that some of the things I'm enclosing within anchor brackets and here I'm enclosing square bracket
[01:22:00] square bracket in the sense this part is optional. If required we can use if not optional. If required we can use if not required we can avoid but whatever I enclosed within ankle brackets that must be given that must be used you can't
[01:22:17] be given that must be used you can't avoid it that is the meaning so now here column table name has to be mentioned then list of column names has to be mentioned along with that the data type
[01:22:30] also has to be mentioned like this all the list of columns whatever you want you mention it and you opened a bracket here round parentheses go for closing of that and put with semicolon
[01:22:46] okay anyhow we write right now but what are the data types are existed yes we'll come to know here the data types see them these are the different
[01:22:58] types see them these are the different data types existed in database data types existed in database So there is one data type called int is there. What is int here? If it is int on that column all whole numbers will be
[01:23:12] Whole numbers that means like numbers 1,00 1 1,000 to 1 2 3 minus values minus 10 minus 20 that mean the number which 10 minus 20 that mean the number which is not having decimal that's it.
[01:23:25] That is then float. What is the float here? All decimal values numbers can be here? All decimal values numbers can be entered. 1.5 2.5 3.5 10.5 something like entered. 1.5 2.5 3.5 10.5 something like that. Then how can I enter the text? So
[01:23:39] that. Then how can I enter the text? So text car is there of string that will be called as string with fixed length. String in the sense some alphabets that collection of alphabets and all that is one thing. Then one more also there
[01:23:53] where car one more also there where car will be there here string with variable length I'll explain it this one just let me finish off these things then if I want to enter the date value date date so
[01:24:09] date value only that while entering that date you have to go with first year then month then in this format you have to enter that
[01:24:21] then time is there time values only time also having format same regular format also having format same regular format hours minutes okay like here seconds that is if you want both the date and time date time will be there
[01:24:39] then if you wanted to store images and all that image is there yes you can store images okay that is one thing if not image are in a database system so text to images. If you want to store in a video or audio or something that
[01:24:55] cannot be stored to direct in a video format and audio format that should be converted into the binary format. So to store the binary data we have lo B loge
[01:25:07] objects binary large objects is there. Okay, that is so as of now you forget about these two I will discuss a little later. Okay, even this image also you
[01:25:19] forget about that. Okay, first we will get the knowledge on Okay, first we will get the knowledge on these data types while creating table. these data types while creating table. Okay, that is so now so how to create a
[01:25:31] table by using this in a sense. So of course here we will be having one table creation. You see that guys here let's take this is okay first of all let me go
[01:25:43] for explaining about one thing what is that you know okay anyhow you got this table here this command okay see there now std name is there std name so in
[01:25:56] this std name see that is nothing but student name it see that is nothing but student name it is see there std name car of 20 I have is see there std name car of 20 I have given and sometimes I'm giving std name
[01:26:11] given and sometimes I'm giving std name okay work pair of sprint so what the difference between these two here both are entering for text only what is the difference here fixed length and variable length
[01:26:26] variable length okay now tell me what is the so here of 20 is what in the sense in std name column maximum you can enter 20 column maximum you can enter 20 characters. That is size of that name.
[01:26:40] Size of the name or else length of the name maximum 20 characters we can enter. How many characters? Maximum 20 characters. More than 20 characters name
[01:26:53] you cannot enter in both the cases that you need to tell. For example, let's say here I'm giving now my name. Let's say that venu gopopal I used let me know ma
[01:27:06] how many characters are there on that venu gopal tell me how many characters are there on the way gopal guys please respond
[01:27:18] the way gopal guys please respond nine is there okay fine it is nine maximum how many characters we have given maximum how many characters we have given 20 so how many characters are free space right
[01:27:32] How many characters free space 11 but okay fine that's okay here also I'm giving way goal here also I'm giving venuer what makes the difference between
[01:27:44] these two you know in storage not explicitly listen carefully in storage in a database storage even though see in car in a memory that
[01:27:56] is in a storage part in a memory though you used nine characters out of maximum 20 characters that memory occupation is 20 characters only. That means whether you use utilize
[01:28:10] That means whether you use utilize maximum 20 characters or not utilize 20 characters data type occupies full space 20 character space will occupy. It will kept it will keep that 11 characters
[01:28:22] empty and won't allow you to store any other data into that in a memory not explicit implicit memory storage. But whereas when coming to here where of 20 whereas when coming to here where of 20 maximum you have given 20 characters but
[01:28:37] how many characters you utilize in this nine character space utilize only nine character space will be occupied in a memory storage. Remaining 11 character memory storage. Remaining 11 character space will left over for further usage.
[01:28:49] Okay. further data store usage that is but so for one more student for example if I give here Ravi here also I'm giving Ravi here four characters though it is a
[01:29:01] four characters CA will occupy 20 characters but here four character space one occupied remaining 16 character space will beed for other perfection space will beed for other perfection so that's why it is called as car and it
[01:29:14] is fixed length it is it is variable length variable in the sense maximum 20 will not be exceeded but how many characters you use based on that the memory will be occupied by the worker but in a car whether you use 20
[01:29:29] characters or not use 20 characters so full memory will be occupied full memory in the sense 20 character space will be occupied now shall we write a create table command with this shall we write down
[01:29:43] shall we write down yes now I'm going to the here let's see that guys Guys, you come back here now. Now I'm working on which database? Feb Now I'm working on which database? Feb 2026 DB. Yes. So within this I'm writing
[01:29:57] 2026 DB. Yes. So within this I'm writing now create table here. Just see. So so now create table here. Just see. So so what is that? Create table. I'm writing that bio data. Yes. Is the table name open bracket. Even you can
[01:30:11] continuously write but I'm writing in multiple lines. So now here I'm taking row number is the column name and int is the data type and put a comma. Yes, one
[01:30:23] column has been done. Guys, listen carefully. Table names, column names must be single word. Multiple words you should not use here. Table names and column names should not go for multiple words. There
[01:30:37] should not be a space between uh table names or column names. If you want space you have to go with a symbol called underscore underscore can use it. Okay,
[01:30:50] that is so that is one thing. Then student name, I'm giving student name as a std name. Then here I'm giving here a std name. Then here I'm giving here pair of 20 characters. That length is up
[01:31:03] to you. Okay. Then I'm giving here now birth date. So it's a date So it's a date I'm giving that is then location.
[01:31:16] I'm giving that is then location. Then I'm using here now car of 20. Yes. So then put semicolon. Yes. Like this you have to write a a simple table create command. So then what should we do? We have to
[01:31:30] select these all lines lines all lines you select and go lines lines all lines you select and go for execute button. for execute button. Yes I got executed the table.
[01:31:43] Okay. So where can I see this table here in this database you have a tables part is there no right click on the tables go for refresh all. So you got a arrow mark here. Arrow mark in the sense one table is there. Click
[01:31:58] on the arrow mark. See you will see the buy data table within that click on the arrow. See that columns. Click on the columns. The list of columns what you mentioned will be shown here. Then indexes foreign keys triggers is there.
[01:32:13] Forget about as of now that upcoming sessions you will come to know that. sensitive. If you follow capital letters, you have to use all capitals. If you follow small letters, all you go for small letters. That means in which
[01:32:27] combination you create table names and column names in the same combination you have to use here. That is there is any restriction at all. Even you write a small letters also. You see there. So whenever you come down here
[01:32:47] create table see that I'm writing create table emphas
[01:33:08] care of from 10. Okay, that is one thing. Then uh we can Okay, that is one thing. Then uh we can go for that salary it is also we can make it float or else make it int only. No worries that even if it is float
[01:33:22] nothing nothing to worry yes like this and I'm selecting this and going for uh executing say I got executed the table. So in a test DB I'm
[01:33:35] going for refreshing all this. Okay, you see there here. So you will see here now guys you see there the small letters only. So here it is capital letters that
[01:33:48] only. So here it is capital letters that is up to you. But my suggestion is that you might be asking sir which one should follow better follow capital letters better follow capital letters that's it. So there is any restriction at all
[01:34:00] whether writing small letters or capital letters anything is okay. So what is another one that here now? Wut again getting an error. What happened? Ward getting an error. What happened? Ward share your screen.
[01:34:19] Got it. Nice. Nice. Nice. Sounds good. going very slowly because you have to settle down here environment and
[01:34:33] executing queries and all that. So that is who already having knowledge be patience in a session. So shall we go for
[01:34:48] also there I'll show you just wait okay first of all basic things we are working basic things because I told I'm repeating once again that it's a first today so that every day one itself I can't go for all at once I'm taking slow
[01:35:04] can't go for all at once I'm taking slow by one by one one by one yeah guys is it okay is [snorts] yeah so now we have created two tables okay in a two different databases okay
[01:35:18] fine that but is the table having any records or rows is the table having any records or rows here no rows are there no records are there so how to add the no records are there so how to add the rows how to add the rows here this is
[01:35:34] very important let's see so for that we have to go for let's see so for that we have to go for some other command. Okay, what is that? some other command. Okay, what is that? See how to add rows to the table.
[01:35:48] So for that there is a command called insert is there. Insert. Insert command is there. Okay. Now has to be used. What is the syntax of insert command? You see that insert. See guys, insert into table name. Insert into
[01:36:06] table name. Then to which column you want to add the data that column names we should get that is column 1 column 2 or something for that columns we have to or something for that columns we have to go for values val one val 2 something we
[01:36:20] have to be entered here. This is in this case wherever you are This is in this case wherever you are having number sorry uh strings and date values string date values and all has to
[01:36:34] enclose in single quotations single quotes quotes single quotes has to be taken up that okay that is one thing we have to go so let's see that how this insert commander
[01:36:48] has to get it that is see there now we got one insert command for the buy data. So the insert into buy data. So how many columns are that column names and for
[01:37:00] that one values has to be given that is let's see now how I'm writing for this. So where that is I'm going to this cloud. So first which database I'm in cloud. So first which database I'm in test DB but I'm going for uh that by
[01:37:14] test DB but I'm going for uh that by data table in which DB feb is there. So that I'm writing like this right now. Okay. So use of FAB 2026 DB first I'm taking that and I'm going
[01:37:30] for that execute that is I executed. Okay here uh whenever you go for this Okay here uh whenever you go for this one by data table is there in this table they'll go for list of columns here. Yeah guys you see there here now insert
[01:37:46] Yeah guys you see there here now insert into bio data. Okay. Open bracket. Then column names. First column role number. Second column std name. Third column birth date. Fourth column location. You should not have the spelling mistakes.
[01:38:02] should not have the spelling mistakes. Remember that my then go for values. Okay. This is and first what is the column? Ro number. Give any role number here. Okay. Any name you can give it that is 10,001. Put a comma. Then second
[01:38:19] one is student name. It's a string. Since it is a string here you enclose in double quotes, single quotes. There you write down that is here. Now so here I write down that is here. Now so here I am writing that Gita Sahastra.
[01:38:38] Yes, put a comma. Then birth date also should be in single quotes. But uh one thing you should remember that what is the date format we should give for the birth date guys? first year then month then day we should not forget that so
[01:38:52] that here also single quotes so that I'm that here also single quotes so that I'm giving right now 20210 giving right now 20210 - 9 - 12
[01:39:04] that is one thing then location that is also a string so that here we go for that provid so entering data is not a case sensitive
[01:39:17] data part you can give anything that is I'm talking about only commands part capital letters or anything so data anything you can enter suppose here let's see that is I wanted to go for like this you see there what I'm doing
[01:39:31] like this you see there what I'm doing you know first one uh so here Gita okay sahastra will take no worries
[01:39:43] yeah so now select those two lines select those two lines and go for execute. Yes, it got executed. Yes, it got executed. It got executed here that is but is that
[01:39:58] row is visible on the table for us in the sense it is not visualizing the sense it is not visualizing it is not visualizing here. So one thing so for inserting of rows we have used one command insert that is okay but to
[01:40:11] see that data is there or not in the table in the sensor again one more table in the sensor again one more command has to be used. Okay. So that is here. Now select command. Select you have to write like this. Select star
[01:40:26] have to write like this. Select star from bio data has to go for it. Select from bio data has to go for it. Select that and click on execute button. Okay. You will see that data. See there. Now this output should come here to you
[01:40:42] this output should come here to you guys. Work on it. that yeah see these two seema the screen which you are seeing now try to execute that one first go to your database where the by
[01:40:56] data table is there where the by data table is there first go to that command that database by using use command then write down that insert command
[01:41:11] give yourself Anything do it that
[01:41:24] current which is very simple thing that in a date format first year next month next date that's it first you have to specify the year specify the year see 2010 is the year hyphen next month
[01:41:39] see 2010 is the year hyphen next month next day in that format only date will be accepted in MySQL. Simple part within the single quote always whether it is a string name or date it it comes at any moment it should
[01:41:55] enclosed within single quotes that is must for a numeric value single quotes is not required you observe that only for 101 I have not given a single quotes for remaining all I have given a string in quotes two strings are there one date
[01:42:11] in quotes two strings are there one date is there that is within single quotes want to date format. So here one thing Moria always storage part like that only that you can't
[01:42:26] change but for visibility perspection in a different formats we can view it for that one we have some functions but storage prospection you can't change that format but viewing purpose we can
[01:42:40] change for that one we have some date functions are there with the help of those functions we can view it in a different ways different ways that is a rule for MySQL
[01:42:58] please let me know how many of you succeeded in setting of rows. One row it succeeded in setting of rows. One row it is.
[01:43:17] also. I hope so. that select star from by data I'm asking about both the commands first execute insert after once it is get succeeded then go for select to see that data
[01:43:32] in that way we have to go so now see here one thing guys what is that you know yeah so this insert command we are executed uh one and we inserted only one row.
[01:43:48] Suppose if I have 10 students data for example let's that so should I need to write 10 insert commands in the sensor of course if you write not a problem for of course if you write not a problem for that that is but with one insert command
[01:44:02] with one insert command multiple rows can be added that is only one row now multiple rows can be added here that is so now I am writing a insert command
[01:44:15] like this here so what is that in the sense just wait uh sense just wait uh uh yeah see there here so what I'm writing you know take this insert command
[01:44:30] command write on here only so insert into bio write on here only so insert into bio data so now here again row number data so now here again row number std name comma birth date so comma
[01:44:44] location so then I'm writing here values So 1,002 then here. So praise then here now.
[01:44:59] then here now. So 2012 - 0 7 - 20. So then here that is so so again what that is hydroad just I'm give
[01:45:13] again what that is hydroad just I'm give yeah one is done then put a comma put yeah one is done then put a comma put there comma and once again you write like this what is that 1,00 to guys wanted I'm giving to show you
[01:45:27] one uh mistake that is again already given role number so then here so here given role number so then here so here now I'm giving Vanilla.
[01:45:42] here what is that in the sense my 2015 what is that in the sense my 2015 10 - 10. So then again here something like I'm giving nellor okay that is one more lurkish. Yes.
[01:45:59] So then one more I'm giving that here. So here 10,4 I have given that. So
[01:46:18] inserting three rows. Yes, see there. Now I'm inserting three rows. Right now Now I'm inserting three rows. Right now at last you have to put semicolon. This is and I'm trying to execute this one.
[01:46:31] I'm trying to execute this. See that I selected those three lines and see it got executed. That is see three rows are affected. Now you see here that is so select start from by data and go for this one. See you got the data here.
[01:46:49] this one. See you got the data here. See there now you have the data. You have here data is there. Right? Now this is this is what just you can take down right now
[01:47:04] see that now that all rows were able to see space problem other is but you are having the data okay like this multiple rows can be added but here one problem is coming so one deadline I did a mistake that to
[01:47:18] show you this is because see the duplicate role number has been given it has been accepted but it should not accept accept it should not accept that why why it is accepted in the sense because yes while creating table while
[01:47:35] creating table we haven't discussed and done with synthesis we haven't discussed and done with the concept called constraints
[01:47:47] okay that is so no constraint so that it has been accepted the duplicates no has been accepted the duplicates no worries that is okay just for this This is what just we do. Okay. So how we are writing the adding
[01:48:02] multiple rows to the table guys? Same thing will you do it that on your side? Do it that how to write multiple rows here by seeing that you just see that
[01:48:15] Arun joti what is that? If I use car type then need to mention the length also of course. So whether it is a car or car length must be mentioned. Now that is compulsory. I told you know that you see
[01:48:31] you see CN ji see this excel sheet the same thing the column specification is same thing for the length if it is a string whether car or car it doesn't matter
[01:48:50] guys you execute this query now execute this query only with the three You at least do it two two rows. You at least do it two
[01:49:02] rows. So now what will be there in the sense? Yeah, I have shown you one small issue. What is that? I have given a duplicate role numbers that table has duplicate role numbers that table has been accepted but it should not accept.
[01:49:15] been accepted but it should not accept. It should not accept that. Okay. Then how that can be taken up? How how can you avoid? Yes, actually always tables you avoid? Yes, actually always tables has to create uh with the help of what
[01:49:28] do you say in the sense constraints only since it is an optional but we have to make it that is a mandatory. So first of all what is a constraint and how to work with the constraints and all that is let's see now here actually we
[01:49:42] go for like this here one thing. So what is that you know let's make it like student is the table name okay here a role number is the column let's see that
[01:49:54] is here now the student name is another column like age is another column is column is there then one more is there like h gender is another column okay
[01:50:08] like h gender is another column okay then location is another column we have like this suppose five columns is For this it is a data type is int and for this here
[01:50:21] where care of some 20 or something it is int of one only I want to represent gender male or female m or f then here it is
[01:50:33] male or female m or f then here it is where car of 10 but on these columns I wanted to put some restrictions that means in the sense some rules what that means in the sense some rules what is that you No. Okay. Here it should not
[01:50:47] be duplicate duplicate and uh should not be what my null empty. That is one thing we have to go. Student
[01:51:01] name it should not be null. That is one thing. And uh it should be it should be between uh 20 to 30 years
[01:51:17] only. Gender it should be Gender it should be should be either Y or F only
[01:51:32] that is location. By default I can hydroad like this I wanted to have some restrictions that is see for every column I have given one rule. So these
[01:51:47] rules actually called as a constraints while creating table. So there is a while creating table. So there is a concept called constraints is there. There is a concept called constraints is there here. So these are the rules to
[01:52:01] implement on table columns. While creating table while creating table for every column if required for every column if required we can place some conditions. Those conditions are called as constraints. What is the first one
[01:52:17] as constraints. What is the first one you have here in the sense null see it allows to empty values in a column. It is a default constraint. nothing but constraints so one constraint will be there that is null
[01:52:32] that is one thing null in the sense empty if you don't any value in a particular column that you can avoid it so that it will keep an empty the second one is not null second one is what here not null is there it will not
[01:52:47] allow the empty values the third one is unique it will not allow duplicate values then one more is there primary key is there. Primary key in the sense guys here see there it is a combination of
[01:53:01] here see there it is a combination of unique and not null constraints. unique and not null constraints. But for every table per see here only one primary key will be allowed in a table
[01:53:14] only one primary key constant can apply for a table. Okay, that is one thing. for a table. Okay, that is one thing. Then check it allows to provide specific values or range of values. It allows to provide specific values or
[01:53:28] range of values. That is one thing and we will write down that default it allows to set a default value for a column while creating table. That is one more. And foreign key it is used to make a relationship between tables.
[01:53:45] Relationship between tables that. So that that is the relationship part. You forget about as of now. See null to default we try out right now. Okay here. So now I'm creating a table. Now I'm creating a table with constraints. So
[01:54:02] creating a table with constraints. So shall we fulfill these things here? Now we will fulfill these things. See what method is role number. It is in it should not be duplicate and should not be null in the sense. So here you will
[01:54:14] get a primary key. Primary key we'll get student name should not be null in the sense here it should not be null in the sense here it should go to not null what that is not
[01:54:26] should go to not null what that is not null is see it should be in between uh 20 to 30 years in the sense check constraint gend gender should be either male or female in the sense here also go for
[01:54:40] female in the sense here also go for check only by default had in the sense no default constraint so these These constants we have to set while creating constants we have to set while creating student table as of now.
[01:54:53] Okay, this is so let me write on this one here. So how can we write on that query? Let's see there in fabbook database only I'm creating a table like
[01:55:05] this. So what is that here? Uh I'm going to take that to take that yes ma that is a h. So I'm writing guys yes ma that is a h. So I'm writing guys here now see there create table student
[01:55:21] here now see there create table student is the table name. Okay then here go for role number it is into now you specify the constraint after data type constraint has to be specified. If we give the primary key that will not allow
[01:55:36] give the primary key that will not allow you duplicate values and null values. Okay that is the second one. What is that st name? Okay. Where care of 20
[01:55:50] close bracket space and not null you should go. See not null should go. See not null one more constant. Now yes gender
[01:56:02] one more constant. Now yes gender it is care of one one character. So how to apply the check constraint here? It should be either M or F other than these two alphabets. Any other alphabet you have given it should not accept. Then
[01:56:18] you have to write a check constraint like this. What is that? You know check here you have to write down in this way. Gender is equal to column name. This is the column name I'm giving equal to in a
[01:56:34] single quotes. Okay. M or again gender is equals to okay F you should give then bracket close and put a comma.
[01:56:49] So that is one thing we have to write down. Okay then is so is it is what here down. Okay then is so is it is what here int. Then again now for this also check
[01:57:01] int. Then again now for this also check check is greater than or equal to 20 and check is greater than or equal to 20 and is less than or equal to 30. Yes. Then location
[01:57:15] Then location so it is work of 10 or 20. So default so it is work of 10 or 20. So default I'm giving that hydroad. Okay. then go for closing and put semicolon
[01:57:29] every column I have given one constraint. So these constraints will be checked while inserting the data that means while adding the new rows. You see there here select these all
[01:57:46] You see there here select these all lines go for executing this is yes I got executed the table creation has been done here so on tables
[01:57:58] go for refreshma here so two tables has to be shown to us right now yes one is by date another one is student table so click on the student table okay here
[01:58:10] columns will come there this is as usual that okay in this way just we can take down okay so first you create this table now by yourself also practice this then uh I'll show you how to go for adding
[01:58:25] rows for this so now we know insert command so let's start inserting the rows into the table so while inserting rows these constraints we will check it guys listen here just listen So the location you see
[01:58:43] especially when coming to this the location is a where there is a default hydroad is there. So while inserting rows if the student location is hydrobad
[01:58:55] only then you can skip the location column remaining columns you can add it column remaining columns you can add it that is suppose if the student location is not hydrobat then you add that location column and insert row. So now
[01:59:09] see there here I'm writing now insert command insert into student. So I'm command insert into student. So I'm giving role number then std name then giving role number then std name then gender then age. That's it. Location I'm
[01:59:23] not including here. Okay. Then values. So I'm giving 1,1 then here. So what is that?
[01:59:35] So what is that? Kavita is the name. Then gender female Kavita is the name. Then gender female then age is 23 something like added. then age is 23 something like added. Okay. This is see the row has been
[01:59:49] inserted successfully. That is see that guys. You want to see that one go for select star from student. Guys this select command has got so much of explanation but as of now we are using like that. Tomorrow we go for complete
[02:00:03] like that. Tomorrow we go for complete discussion about that is yes we got the data you see there actually in insert command in insert command we haven't added location but we got the location had because while
[02:00:16] got the location had because while creating table we made that hydrobat creating table we made that hydrobat okay that is okay guys now see without doing any changes if I execute once again this insert command right now will
[02:00:30] it be insert the row into the table in the sense of will it be inserted here as of now without doing any modification without doing any modification it is already done we got the data also
[02:00:45] once again I'm adding here with the same role number 101 already with the 101 role number one student is there once again I'm inserting here so it should not be see that it should come an error see that we got an error here
[02:01:02] In the below you see there red mark has came that that is an error. What is that error in the sense you see there? So 1062 duplicate entry 101.
[02:01:14] 1062 duplicate entry 101. What it is that duplicate entry 1,1 it is saying that one to you already 101 student role number is already 101 student role number is there. Okay. It's not alter hersaden.
[02:01:27] there. Okay. It's not alter hersaden. It's a changing of value only. Alter is a different I'm talking about the data not the column. Okay. So now here I'm adding another one changing the value here you can know
[02:01:41] 1,002. Now you see there I'm checking the Now you see there I'm checking the constraint. Uh here let's let me change constraint. Uh here let's let me change the name. Now it is morally and it is a
[02:01:53] male person but accidentally I have given Y but we have given check given Y but we have given check constraint either M or F. Okay. So select this and go for execute once again. See there row is not once again
[02:02:08] got an error that what is that? See there check constraint. Check constraint is violated because you have given in the place of M accidentally N. So not
[02:02:20] accepting that is okay. So that M has to be given. Then one more the age should be in between 20 to 30 years. I'm giving 34.
[02:02:33] Let's see that 34 will be accepted or not. we got at that. See check two constraint is violated. Student chk.2
[02:02:49] is violated. Student chk.2 is violated. Yes. 34. But it should be in between 20 to 30 only. So that now here you can even if you go for 14 or below 20 also it won't accept okay that is so I'm [snorts] giving 21
[02:03:05] okay that is so I'm [snorts] giving 21 now the row will be accepted yes a row has been inserted see there now two rows are there that means the constraints what we defined that every constraint is working here
[02:03:20] so you have to taken care of that once you define a table with the constraint finds while entering data while inserting rows that proper data should inserting rows that proper data should be maintained here. If any uh like data
[02:03:35] is invalid or the row will not be inserted that you need to check. So now with the insert command you check yourself that with the insert command yourself that with the insert command you check that is
[02:03:49] okay. So that that is the importance of constraints. So without constraint we should not create the tables. If a we should not create the tables. If a table is uh if in table one person have
[02:04:02] a different locations then the default will be work for that person then houseer. Yeah, no worries. That suppose that means so suppose another person having another a different location right you
[02:04:16] another a different location right you are asking Moria sense here multiple values cannot be inserted into one place no okay suppose
[02:04:33] suppose I have one more student is there for example let's see that is if I have one more student that student have a different location at that moment. What different location at that moment. What should we go? The insert command has to
[02:04:47] go for changing of that is. So what we should we go for here now you go for add the location. Okay. And here you add the location.
[02:04:59] Then what happens here? uh the location will be automatically comes to the Delhi by replacing of that comes to the Delhi by replacing of that hydrobad let's go for here now 1003
[02:05:12] okay here Navia is the name Navia is the name okay female okay 21 let it be that okay okay female okay 21 let it be that okay here so now you go for here now you
[02:05:26] execute this one where yes row has been inserted and here. Uh
[02:05:38] go for this is there. See the third student will come here. That is Yeah. student will come here. That is Yeah. See the third student del has came. But in the place of delhi like a delhi some other location in the sensor you
[02:05:53] have to put a comma and you have to add as a string that is
[02:06:06] So I hope that it is cleared with everyone. guys. So this is today we completely discussed about how to create a
[02:06:22] database, how to change from one database another database and how to create a table without constraints and we have seen how to create a table with constraints and [snorts] how to add the rows into that table with the insert
[02:06:35] command, insert command with a single row, insert command with multiple rows. These are the topics we discussed today along with the introduction of along with the introduction of databases. Today's agenda is so SQL
[02:06:48] commands guys all the commands we'll go for that. See yesterday we had some commands how to create a database how to create a like what you say that is table how to change from one database to another database that we have seen.
[02:07:03] Okay. So these all things we did it that okay how to insert the data into table. So that is these all things we have seen that is rest of the commands are there. Let's we'll continue with that commands here
[02:07:17] we'll continue those commands. So open your MySQL workbench on local machine and you create one database how we have done yesterday in the same way you create one database create one database here that is so now
[02:07:35] here I'm creating one database create database not have any databases if you already created no worries I'm creating web 2026
[02:07:48] 6 20 26 DB something like how I created in a cloud same thing I'm taking that yes this database has been created then
[02:08:00] enter into that database so here you so to see that in a schema section administration and schemas is there in the schema section go with the refresh
[02:08:12] the schema section go with the refresh you will be having that Fab now yes Feb you will be having that Fab now yes Feb 2026 6 DB is there here. So now go with 2026 6 DB is there here. So now go with the what like use command to access into
[02:08:24] that. So this is the one thing just we took. Now no tables are there because it's a local one. Okay, it's a fresh database. No tables are there. Now we will create the tables. We will create the tables.
[02:08:40] So now I'm going to uh discuss some other commands. So, so already discussed other commands. So, so already discussed commands is what? That
[02:08:55] already cu s discussed commands is what that? cu s discussed commands is what that? So, create database has been done. Then changing database has been done. Okay. Then uh creating table has been done.
[02:09:11] Then inserting rows has been done. Right. These are the things guys please let me know any queries on behalf of these all things
[02:09:34] show key in the sense it's a F5 that I'll give you because of Chandra Praash one thing I'll tell you here just wait okay so now we are going to discuss the commands here. The next command we go
[02:09:47] for update. Update command has to discuss. Then delete command has to discuss. Then drop command. So then here alter command.
[02:10:00] So then here alter command. Okay. Then describe command. Okay. These are the commands. After completion of these commands, then we go completion of these commands, then we go for here. Now select the command. Okay.
[02:10:13] like we have some commands select command is very big command guys so command is very big command guys so there we have to uh what do you say that is lot of clauses and all that is that's why I kept into the separately
[02:10:31] discussing uh commands that this is what just we are having so if everyone is okay then shall I start the discussion Shall we go for with the first update
[02:10:45] Shall we go for with the first update command?
[02:11:05] what is an update command use? Yesterday we have seen insert command. Yesterday we have seen insert command that insert command having their row adding the new command having their row adding the new rows into the table. So at the same time
[02:11:18] rows into the table. So at the same time whenever you want to modify the row whenever you want to modify the row values modify row values or change the row values then how we can do that in the sense here the update command can be
[02:11:32] used here. Okay that is let's see guys here. So what I'm writing now this one this command is used to this command is used command is used to this command is used to change row values
[02:11:48] to change row values or also allows to perform calculations allows to perform calculations on specific specific uh columns
[02:12:01] specific columns of table. Again that is one thing you have to go Again that is one thing you have to go for now. Okay, this is what is its syntax guys? Listen there. What is its syntax in the sense guys? This command
[02:12:16] has to use like this. Update the table name has to be taken and set here column name has to be taken and set here column name has to be taken equal to then here
[02:12:28] value or expression. Expression nothing but formula and if required we have to discuss here. Now some clause called where clause. Yes, this is what the simple command we are having.
[02:12:44] Simple command we are having here. This is one thing we go. is one thing we go. So to work on this to work on this one first we create one table. So uh to discuss all the clauses of this update
[02:12:58] command I'm creating one table. Within the table we add some sample data. That means we insert some rows over there. After that we will start updating the applying the update command on that. Okay, that we do here that is one thing.
[02:13:15] So, so here let's see that uh how we can do so here let's see that uh how we can do that is just see that guys now I'm going to this here and I'm creating one table. The table is right now marks table I'm
[02:13:29] The table is right now marks table I'm creating. So create table marks. Marks is the table name. As usual guys, I'm giving role number. Enter. And I'm applying [clears throat] primary key.
[02:13:44] So no duplicates, no null values. Then [snorts] student name I'm taking S name is the column name. Then I'm taking workar of some 20. Okay, that is one thing. Then exam I'm taking and workar of 20. Usual I'm not applying any
[02:13:59] constraints here because my focus on update command that is even if you want you can go for that uh constraints also what is that here you apply it should not be null and it it is also should not be null like this
[02:14:15] be null like this then afterwards guys you see here that uh subjects I'm giving so I'm giving column names like subject one only it is column names like subject one only it is int if you want here you can give uh
[02:14:28] A check constraint student marks should be in between 0 to 100. It should not accept below zero. It should not accept above 100. Like that you can give it. Okay. So I'm writing now check subject one greater
[02:14:46] writing now check subject one greater than or equal to zero and subject one less than or equals to 100. Check constraint. I'm applying the same thing constraint. I'm applying the same thing you go for with the subject to I'm not
[02:15:00] writing my subject names like English, math, science or soap or something like math, science or soap or something like that. I'm just using that is that. I'm just using that is then I'm giving total marks take integer
[02:15:13] then I'm giving percentage avg it's a decimal float and I'm giving here result it's a car of four what is this result in the sensema pass fail p a ss four
[02:15:27] letters f a l fail four letters so car can use because we are using full length of string So that car is okay. So this is guys. I hope that everyone understood about that query.
[02:15:43] Okay. This is now I'm creating this table guys. See there I created the table. See there now the table executed successfully and right click on the
[02:15:55] tables of database and go for refresh here. Then go here that is see marks table is there. See you are getting the structure. See the now the columns you
[02:16:07] can see these columns here. Yes, we have that. So guys try to understand once we create a table like this. Okay.
[02:16:19] In this table whenever you are going to insert the rows in this table whenever you are going to insert the rows row insertion. So while inserting the rows avoid this total, average and result because this total should be calculate
[02:16:37] and average also should be calculate and result also should be calculate. Okay. Pass fail that is so that how that uh process can take how
[02:16:49] so that how that uh process can take how that process can take here. Yes. First of all insert the rows. So yesterday I shown you how to insert multiple rows. insert into marks. List out the column names which you
[02:17:03] List out the column names which you wanted to add rows here. Okay. Then subject one then subject two up to only then you go for that values.
[02:17:16] So one. So here uh sur is the name. Then here quarterly is the exam. then something like 67 and 89.
[02:17:29] Okay, I'm placing comma. Let me add five rows here. So, exam is same. I'm changing the marks here now. 27 it is 59 and 77
[02:17:44] 59. 59 or also 49 and this is okay here 29. Okay. 90 and it is 80. Yes guys, see
[02:17:58] Okay. 90 and it is 80. Yes guys, see guys here like we are we took this one and I'm executing this. Yes, five rows are see five rows are added. So that is are see five rows are added. So that is one thing we are getting. Let's see the
[02:18:11] one thing we are getting. Let's see the data select start from whatma that is marks can do it now. See there here yes we have some five students data is there see total column average column and result column is
[02:18:27] passed the values for that because we have to calculate that have to calculate that we have to go for calculating that is so I hope that everyone understood up to this this commands all everything we did
[02:18:42] yesterday only okay now create table date inserted data that is Now come down date inserted data that is Now come down to working on this update command. What I did that my change the row values or allows to perform calculations on
[02:18:57] specific columns of table. Now I'm going for calculations. What is that? You know here whenever you come down here now so this total column has to fill with the total marks by adding of subject one marks and subject
[02:19:13] two marks. It has to go for subject one plus subject two. subject one plus plus subject two. subject one plus subject two. See that guys I'm writing marks set to which column need to update right
[02:19:26] set to which column need to update right now total. Total is equal to subject one now total. Total is equal to subject one plus subject two. That's it. Okay. That goes to that subject one column and subject two column. It respective row
[02:19:41] values will fetch and that updates onto the total column. the total column. all the rows not only five 500 5,000 it doesn't matter it will be updates over there to select that and go for execute
[02:19:56] here see there guys five rows are affected affected five rows are affected that
[02:20:09] here the set is one keyword which will refer the column on which column you are updating that is [clears throat] that what is total here what is total total is what here it's a column name of table see it's a column name set refers
[02:20:26] to particular column for that one I'm doing some expression here calculation the result will be updates on the total column that is what the set
[02:20:38] see we I got right now five rows are affected now check here now by going to select star from marks. Now see there now we got a total marks are here guys. 156 86 126 96 170 something got it
[02:20:56] guys. 156 86 126 96 170 something got it or not? Guys is this query is understood by everyone.
[02:21:08] Is that update queries understood by everyone guys?
[02:21:21] average. So what I'm doing right now there update max set of average equal to can we write like this here? Now subject 1 + subject 2 by 2 make it that is a 2.0
[02:21:36] 1 + subject 2 by 2 make it that is a 2.0 zero or something that okay like this we are taking see again five rows are affected so check now here affecting rows that see we got the total marks sorry average
[02:21:51] marks we got it so you may ask a question here sir why you put here now 2.0 zero why can't you go for two actually one thing guys uh here data
[02:22:03] type require things will be fx here now so subject one data type is int subject two data type is also in subject two data type is type is also in subject two data type is also in if I go with like this
[02:22:18] [clears throat] two is also in two is also in okay so integer value with any arithmetic operation of another integer will give
[02:22:30] the integer only. For example, if the average goes here For example, if the average goes here like 57.5 something as came that 0.5 will not be written here. Now if it is goes like this and but if it is going
[02:22:46] for 2.0 this is part this is everything integer and this is the float. So integer with float integer with float you do any
[02:22:58] operation the return value will be the float only but here we haven't get any opportunity to get the decimals see that 56x2 in the sense 78 only 86x2 only 43
[02:23:10] exact values are getting 126x2 in the sense 63 same thing is there okay but if you have such kind of values that also will come this is there
[02:23:26] two update commands guys listen these two update commands. So updating all the rows of the particular column all the rows. But right now see that guys here one point what is that you know result
[02:23:44] point what is that you know result uh should be [clears throat] pass if every subject
[02:23:56] having a greater than or equal to 35 marks. This is the condition has been given. See result column is there. In this result column pass should be filled.
[02:24:09] pass pass pass pass pass pass pass pass pass pass pass pass pass pass when if every subject having above or equal to 35 marks okay so that means so a condition is specifying here guys listen carefully
[02:24:23] condition is specifying so now I'm writing actually the query in this way try to understand now update marks set result equal to no
[02:24:36] marks set result equal to no calculations directly value pass. Suppose if I execute this one in entire result column will get pass. Enter result column will get pass that. But it should not come like that. Okay.
[02:24:53] If every subject if every subject marks having 35 or above 35. But you see here now the first first student 67 89. Okay. Past will come.
[02:25:08] Second student Romesh is there. See subject one having 27 and second subject 59. One subject is having below 35. So it should not fill the pass. That should it should not fill the pass. That should go for empty only. Then 7749.
[02:25:23] Okay. Filled. Then 6729. No. Second subreddit is fail marks is No. Second subreddit is fail marks is there. No pass there. 80 and 90. Okay. there. No pass there. 80 and 90. Okay. Fine. This is what. So here how to
[02:25:36] Fine. This is what. So here how to specify that condition? How to specify that uh condition here? The condition has to specify it here. Now how that condition can specify? Yes guys. So here where clause is there?
[02:25:55] Okay. So note you can take down here where clause is used to specify what now that is specify
[02:26:07] conditions how it is let's see now you have to how it is let's see now you have to write like this where see that where so what the condition here every subject should have 35 or 35 mark above 35 what
[02:26:23] are the column name subject column names here. Subject one, yes, greater than or equal to 35 and and subject two greater than or
[02:26:37] and and subject two greater than or equal to 35. You have to write lines. How many conditions we mentioned here? Two conditions. One condition on column one, another condition on column two. Two conditions must be satisfied. and
[02:26:50] operator and in the sense first condition must be satisfied and second condition must be satisfied. Not only that even if you specify some other conditions in between one condition to another condition and in the sense all
[02:27:05] another condition and in the sense all conditions must be satisfy all all conditions must be satisfied should go like that that's the query now let's see we have a five rows okay only three rows has to get update see there
[02:27:20] now wherever the conditions are satisfied see three rows are affected is You will check that here guys. You see there here and go for that is see there
[02:27:33] first one got pass. Third one has got pass. Second one four fifth one has got pass. See in second row first subject is fail See in second row first subject is fail marks 27. In fourth row second subject
[02:27:46] has got fail marks 29. But this is also has to filled with fail. No that also has to filled with fail. No that also filled with one value.
[02:27:58] for fail. We have to write a condition for what We have to write a condition for what here fail [clears throat] that is. So then how to write that is one more update command. Here I'm writing there
[02:28:12] fail where subject one less than 35 subject one less than 35 or not andma are because to the pass every subject should get above or equal to 35 for a failure any one subject if
[02:28:28] it is having less than 35 it is fail not all. So that or either this one or that one. Either this condition true or that any one condition is true and in the sense all conditions are true and in the sense all conditions are true
[02:28:46] or in the sense any one condition is true. It goes like that. Let me check right now ma. So this is and I'm executing now. Yes, two rows are
[02:28:58] affected. Shall we check that? See there going for Shall we check that? See there going for this is here. Yeah we have a fail. We this is here. Yeah we have a fail. We have a fail. See entire table has been
[02:29:11] filled here. See there now entire table has been filled over there. Uh yes yes I think so you all getting an
[02:29:25] error for update command right? Yes. Yes. Is that update command is like safe Yes. Is that update command is like safe mode something like that is giving yes it's an update command is that yeah fine no worries before executing the
[02:29:39] update command guys so just one second because for security constraints so update and delete command directly won't work so for that one command has to be work so for that one command has to be used here I forgot that
[02:29:58] set safe update in MySQL so one simple command is there. Just wait. Yeah.
[02:30:10] update command, this command you execute first, write on set save updates equal to one, you just execute then afterwards. So go for executing the update commands. You will uh it will execute for you. This is the temporary
[02:30:25] solution. Permanent solution I'll give you. First you do this one. First you do this is because of avoiding unnecessary updations and deletions directly. It won't allow you to update the rows.
[02:30:47] One in the sense enabling updates, zero in the sense disabling updates. That is but guys one thing you have to go here. Let's see. For example, let's say in this table in this table here, if if I updated the
[02:31:04] marks, for example, this marks not 27, it's like for example, it's say like 77 it's like for example, it's say like 77 or 47 or something, then automatically uh this total average result should get reflected. But here automatic updations
[02:31:20] are not possible. Here automatic updations are not possible. Remember that. Okay. Here automatic updations
[02:31:34] are not performed in MySQL. Remember that. For example, see, so how can I modify this marks? For example,
[02:31:47] I'll show you now. How can I modify only these marks? Can I get like this here? these marks? Can I get like this here? Now let's see update marks set of
[02:31:59] subject one equal to let's say that it is a 47. for like this all subject one marks will get 47. Only for the student we should go. So that compulsory you need to change the you need to go for a
[02:32:14] condition where role number is equal to one or two. Will it work or not? See there whoever having a
[02:32:33] sharing sorry this updating now see there I got updated that one go with the there I got updated that one go with the select statement guys see there yes it is updated but still the marks are same so total marks are same average marks
[02:32:49] are same and fail result also same it is not modified So then what you need to do you have to go for executing all these queries once again all queries has to go for that is okay.
[02:33:04] So that is one thing you have to go for now. So what are that you know here. So first you select these two multiple queries also can be selected. See there one row is affected that.
[02:33:19] is affected that. So then we can go with the pass command. Let's see that is here. Yes, one row everything I did it my then check that everything I did it my then check that row is modified or rot here. H see there
[02:33:34] 475 so that 6 it is a 53% pass we got it
[02:33:46] means once you do any modifications on calculated columns again you have to re recalculations has to be done that that mean those queries you have to go for executing once again that is my point here that is my point there now tell me
[02:34:02] guys one thing I wanted to change see this is the query guys here now change this is the query guys here now change the student name as like Chandra Babu
[02:34:19] role number who are having the role number is here like five. Now tell me the query here now whose role number is 105 that is for
[02:34:33] that question what is the answer I wanted to change name as a chendra babu whose role number having five that means so this nar name I wanted to change as a chendra babu how that can be done
[02:34:55] yes I got karan has given answer has given answer and what about others guys given answer and what about others guys please into the double quotations nav navindra that should be number so that
[02:35:12] leave it like that without double quotes even that you should not use the double quotes you should use the single quotes for chundrau also it should be single for chundrau also it should be single quotes not the double codes.
[02:35:32] received is correct. The little correction on Navin RAR in the place of double quotes, single quotes and for number no need to have quotes and for number no need to have any single quotes.
[02:35:46] that. See there already given that uh some of the learners. some of the learners. So update mark set of yes name is equal to chandra babu
[02:36:00] to chandra babu where role number is equal to5 where role number is equal to5 you should get like that.
[02:36:15] execute it that's about update command guys. So how the updations will be done here? How the updations will be done? That is one thing we are having.
[02:36:36] I'm moving on to some other command guys here. So what is another command in the sense? So here delete is the command. Delete command is there. This command is used to delete
[02:36:54] This command is used to delete rows from table. Delete rows from table. Whenever you want to remove the rows, not the columns, remove the rows. Removing the rows, then we go with work, we can work with the delete command
[02:37:10] here. So now the command syntax will come like this. Delete from table name. Okay, this is if required a where clause
[02:37:25] also where clause this is the command we have where clause this is the command we have to use. See that guys here. Let me going for like this. But of course just see there.
[02:37:40] like this. But of course just see there. So how can we use this in the sense of So how can we use this in the sense of delete from marks? Delete from marks is delete from marks? Delete from marks is suppose if you execute this one. If you
[02:37:53] suppose if you execute this one. If you execute this it will delete all rows from table. All rows empty table will give you
[02:38:05] empty table will give you that one. Table will be there but no data will exist. No rows are existed. That is one thing. That is one thing. But here, so delete from marks. See
[02:38:19] But here, so delete from marks. See there where uh so result is equal to fail. If you write like this, what will go in If you write like this, what will go in the sensema here? It will delete.
[02:38:34] the sensema here? It will delete. It will delete all rows whose result is failed. Whoever failed those all students will
[02:38:46] be deleted. We have only one failed student is there. No let I'm executing this. I'm not executing the first one because deleting rows. No, if you delete have to insert and update everything has to be go for that. Yes, I executed. You
[02:39:01] see there one row affected. So now you check that here So now you check that here select start from sorry max do it that right now there see that location details is not there see 1 3 1
[02:39:19] 5 is there in between 1 3 1 5 1 4 is not there there that the simple command delete that the simple command delete that is delete command is there here
[02:39:32] simple Delete command is there. That's it. Delete command. If anybody wants to check that, you go with this command. I'm giving delete command here. Go with this.
[02:39:47] Yes, ma'am. Try to execute that command. Don't delete all the rows. Don't delete all the rows. So, next command we go for now here. So, one more is there here. Now uh what do you say that is alter command we go
[02:40:03] for here first alter we discuss then we come to the drop first alter command what is this alter command in the sense so with the help of alter command guys we can perform multiple actions it is somewhat big command okay what are that
[02:40:19] multiple actions in the sense for example let's see this is the table we are having so for this table I wanted to add some other new columns. If you want to add a new rows, okay, insert command will
[02:40:35] work, insert command will work that. But here I want a new another column in after the result. I want a grade column for example. So how can we go for adding a new
[02:40:47] So how can we go for adding a new column? That is one thing. Or else also I wanted to delete existing column. Row deleting in the sense delete command is there. Row deletion just now we have seen delete command is there. How to
[02:41:00] delete a column? I want to remove the column itself. That is one thing. I wanted to rename the column column name. I wanted to change column heading column name. I wanted to change like we have. So much of things are there. Let me
[02:41:16] So much of things are there. Let me write on that. So here, write on that. So here, so this command is used to perform so this command is used to perform different uh uh modify operations.
[02:41:31] different uh uh modify operations. Modify operations on table structure not on table rows. Table structure. Table structure in the sense columns that is one thing. So what is it? So syntax in the sensema alter table table
[02:41:48] name and here we have some keywords is there add modify drop here we have to write down expression.
[02:42:00] This is the generic syntax of that alter command. What are the actions we do here? You know first one adding new columns.
[02:42:12] The second one is deleting existing columns. This is the second action we can do it here. Third one here like modify
[02:42:25] here. Third one here like modify data type of the columns. Okay that is one thing. Fourth one, adding a primary key to the existing columns. Existing columns. That is one thing.
[02:42:42] Then the fifth one is here. Dropping a primary key. If you don't want, we can drop that. Then the sixth one here renaming
[02:42:55] one here renaming renaming existing column names. existing column names. These are the different six operations we can perform by using of alter statement. Yes, to do this work what we do right
[02:43:11] now you know we go here uh first of all create one empty table empty sample table we create on that we will work on it here. Okay. So that is because to
[02:43:23] implement these all while creating table we we will made it some mistakes. Okay, that how can we correct it? Because of that I'm creating a one more table here.
[02:43:35] Let's go for here. Now alter no here. Yes, alter command. So alter no here. Yes, alter command. So now create a table. Okay, now sample is
[02:43:47] now create a table. Okay, now sample is the table name. Let's see that SN the table name. Let's see that SN it is int and I'm giving the not null. I'm not giving wantedly primary key. Okay, not null later we add that then
[02:44:02] yes name where care of sum 20 where care of sum 20 or else do it like this car of 20. or else do it like this car of 20. So that is one thing. Now let's see here
[02:44:15] So that is one thing. Now let's see here then four uh yeah city then four uh yeah city it is work of 20 or 10 or something something okay fine with these three columns so
[02:44:28] columns so I created a table let's see now yes so now whenever we go for guys here you just see there that
[02:44:40] yes so this is the Marks table. This is the sample table. There is no data. Okay. These are the columns which we are having that is this. Yeah. Fine. And see guys here there is a command called describe is there.
[02:44:55] There is a command called describe is there. What is the describe command? You know that. [clears throat]
[02:45:07] Describe command. No no no no no it allows to show structure of table structure of table that so how this can be taken up in the sense a very simple command it is so
[02:45:24] sense a very simple command it is so describe table name table name we'll go for it this is in this describe also this ri i is an optional de also works here let's see now there we created a table node code. So, D E S C R I B
[02:45:43] E. So, sample and if you go and execute this guys, you and if you go and execute this guys, you see there, see it shows the structure. Structure means what? Column name, data type, any constants you have applied,
[02:45:57] those all will come into the here. Now, see there now field type not null or null. Can any keys are there? Primary key, foreign key, something. Have you set any default value or not? If anything is there for any column default
[02:46:11] that will be shows here something will come that you want to see that see there describe even de also works my here marks for any table you can see that is marks for any table you can see that is not only that see there now so this is
[02:46:26] one we are having just see there means it has been applied the primary key okay now like this remaining all is fine that so not null not null has been given that so not null not null has been given so no null remaining all or yes has been
[02:46:40] so no null remaining all or yes has been given that is like this you can see what we can call structure of table okay fine now we are focusing on this now let's see one by one I'm taking mat add a new columns
[02:46:56] columns add new columns so by name phone number add new columns so by name phone number what is phone number and pin code let's see that these two columns I wanted to add
[02:47:09] to which table to the sample table. These all are coming working with the sample table only. So let's see that how we write that alter table sample see add
[02:47:23] first phone number I'm taking only phone where carara 10 yes one column I'm adding here just see there now simple there is no different
[02:47:36] action here no after add you have to keyword the column name new column name and it data type that's it and it's data type you have to go for and it's data type you have to go for like that only simple command it is
[02:47:51] yes ma'am so column has been added that but how can I see that you have to check but how can I see that you have to check with the describe only with the describe only see own has been added here
[02:48:03] now you want one more column adding same command one more time you repeat it command one more time you repeat it so here uh what is that pin code pin so here uh what is that pin code pin code is Six digits pin code. Yes. Six.
[02:48:16] code is Six digits pin code. Yes. Six. I'm adding that also. Yes. You see there that here. Yes. Pin code like see there the columns Yes. Pin code like see there the columns are extending here. Now
[02:48:30] that is what in this way you can add the columns. You can add the columns here. This is one command we are having. Then one more. What is that? remove what
[02:48:44] Then one more. What is that? remove what that is phone number phone column I want to delete the phone column city is there for a city pin code is enough I is there for a city pin code is enough I don't want phone then here alter table
[02:48:57] sample drop what you have to go for drop column you have to write like this drop column column name phone you have to write like this that's why I have written here Now expression in the
[02:49:13] have written here Now expression in the syntax because every aspect is going to syntax because every aspect is going to be different uh writing of command drop column column name phone column I don't want yes
[02:49:29] it has been executed ma go for once again describe here see no phone is there that means no phone column is there that has been removed that has been removed that
[02:49:46] okay so that is one thing here we are checking then one more so here you see that whenever you scroll down I want to change the data type of this s name column
[02:50:00] what is that car is there I want to convert into the work car so that change convert into the work car so that change the data type of yes name column to work the data type of yes name column to work of 20 something like this
[02:50:15] of 20 something like this with what we can call that not null with not null constraint suppose if I write like this the query how can we go
[02:50:28] for that very simple that guys alter table sample see modify column what is the column name yes name. Okay.
[02:50:41] What is a new data type? You just give it where car of sum 20 then not null. Something like this. That's it. Here it is. Null constraint is there. I
[02:50:53] added the not null constraint. Select this and execute. Now this one. Yes, it executed successfully. That is right now there. Go for executing that
[02:51:05] right now there. Go for executing that uh command. Yes. Going for this. Go for this one. See that the car 20 has become aware 20 and null become as not null. aware 20 and null become as not null. No.
[02:51:21] So how the structure we are changing that is we are not working on the data we are working on the table structure. Table structure that is then we haven't added
[02:51:34] the primary key here. So add one more that is primary key constraint. that is primary key constraint. Add primary key constraint to to SNO column. Then what should we write here? Uh yes
[02:51:50] Then what should we write here? Uh yes mother write alter table sample add. See mother write alter table sample add. See that add primary key of to which column you are giving a primary key? Yes name column sorry yes
[02:52:05] primary key? Yes name column sorry yes number so that you just see this one here yes it has been executed
[02:52:18] whether it is added or not how it will come to know see p comes here now p means primary key it is p means what it is primary key that one part of we are
[02:52:32] is primary key that one part of we are having existed if you're doing anything modification that not that one it is add
[02:52:47] only that is no primary primary key is not there for a table so you are adding a new primary key that is so no updation only add
[02:53:04] I don't want primary key I don't want primary key in this sense so actually the primary key have given to the wrong column so remove that primary key to the existing column then add primary key to any other column which is suitable one
[02:53:18] such kind of things if you want to do how to remove the a primary key here in how to remove the a primary key here in the sense just see that delete a primary key delete primary key simple thing that how to delete a primary primary key here
[02:53:32] alter table sample drop what mother drop simply going for that primary key you no need to specify
[02:53:44] the column name because as I already told you primary key constraint will go told you primary key constraint will go for only one only once in a table for any column that so that it will check the primary key to which column it is
[02:53:58] the primary key to which column it is there for that it will be deleted Okay. So how can we see that whether it is deleted or not?
[02:54:12] is deleted or not? See there no P here. Previously it was P See there no P here. Previously it was P that is primary key. Now no primary key that is primary key. Now no primary key like this. So we are having this one.
[02:54:26] This is the one. Just we go for it right now. And finally we go for like this. What is that? Rename a column that? Rename a column from s name to std name.
[02:54:43] Rename a column from s name to std name. S name column want to change the std name. So then how can we go for that is alter So then how can we go for that is alter table sample rename.
[02:54:59] So that I would have told you that yes name [clears throat] name [clears throat] into std name I think. So that is
[02:55:16] what happened to this is the name column. Yes. Name old column name. Uh yes.
[02:55:35] see this one here. So now the column name becomes here. Now std name. See name becomes here. Now std name. See there.
[02:55:47] So like this. So alter command works. Now this is the way the alter command works over here. That is then the next one we'll go for here. Now there are two commands is there. The very first command is right now drop.
[02:56:03] very first command is right now drop. Drop command is there. Now okay. What is the drop command here? The drop command goes like this. Uh this drop command goes like this. Uh this command is used to delete any object
[02:56:19] command is used to delete any object from database from database including databases. So from database that we took here now any object that means it's not only to the tables. So we have some other
[02:56:34] objects are there objects in the sense you will come to know in the next coming sessions. See there here if you're taking on that table is there then views will work then stored procedures will work then functions will work to delete
[02:56:48] middle of that we'll be having some indexes will come there then triggers will come there these all has to work on here the next coming sessions. Okay. So these all and that to even database.
[02:57:03] So these all are here now as a part of that here now. So to delete anything that here now. So to delete anything that we go for the drop. So we have to write down the syntax like this. Drop object.
[02:57:17] this. Drop object. Okay. Then here object name. Object name that suppose to delete a table. To delete table delete table what should we write? drop table name
[02:57:32] in a simple one that is suppose to delete a database so I don't want a database in the sense you can write down like this drop database then database
[02:57:44] name database name you should get like this if it is a view drop view view name if it is a procedure drop procedure procedure name if it is a function drop function function name if it is a index
[02:57:59] function function name if it is a index drop index index name. Okay, like this it goes here. And now uh earlier to this so we discussed about delete command. Earlier to this we discussed about delete
[02:58:12] command. What makes the difference between a delete and a drop in the sense between a delete and a drop in the sense delete will delete the only rows but table will be existed. You delete all the rows but table is existed but
[02:58:25] drop is not like that. Once uh you use the drop command, entire table table will be deleted. Entire table will be deleted here including data. Table deletion of this including data it
[02:58:38] will be deleted. That is one thing. And one more point here. So whenever you delete some rows in a table, whenever we delete some rows in a
[02:58:50] table, whenever we delete some rows in a table, what must sir? How to get started with MySQL workbench in case practice lab is not working. Okay Proy just I'll take to your screen just wait for a few minutes. So here
[02:59:06] whenever you delete the delete the rows you have a chance to get back those rows deleted rows once again we have to discuss some commands called roll back that moment I'll show you that that means there is a chance to delete to get
[02:59:22] uh there is a chance to get back the deleted rows but once you delete the table that we cannot get back that we cannot get back here so that drop command will delete the table permanently. You cannot revert it.
[02:59:40] Whereas if you delete the rows from the table, you delete the rows from table. So there is a chance to reverted that. There is a chance to reverted that here. Okay, that is one thing. There's a simple command drop. There is no any
[02:59:56] wear clauses or something else there here. I'm not deleting any dropping any table right now. Of course, if you want, you can drop table here. You see there the sample table will drop it here. You just go for drop table sample.
[03:00:13] I'm just going for this. Just to go for like this much. See there is no table sample here now. See only marks table we have here. So that is what we get it. And one more also there here.
[03:00:27] also there here. Okay. That is called here now truncate. There is a command called truncate is there. there. Truncate command is this command
[03:00:39] Truncate command is this command is used to delete all rows from table. is used to delete all rows from table. All rows from table.
[03:00:56] So that means here what is that trate? This one you go for that truncate table table name like this you should get that is it won't delete the table. Listen carefully it won't delete the table. It
[03:01:11] carefully it won't delete the table. It will delete all rows from table. will delete all rows from table. Delete all rows from table here. Now what makes the difference between delete and truncate?
[03:01:24] So delete and truncate. uh the difference between delete and truncate. Delete and truncate in the sense let's
[03:01:37] see that delete allows to delete specific
[03:01:54] rows based on condition. Okay. along with the all rows. Along Okay. along with the all rows. Along with what? All rows. with what? All rows. That is one thing. Whereas
[03:02:10] whereas truncate truncate deletes all rows. That is the first point. Okay. Point number one. Second point.
[03:02:34] roll back to get back uh deleted rows to get back uh deleted rows to get back deleted rows. Whereas what method is uh truncate
[03:02:50] is not these two points you have to remember that is these two points has to be remembered with the help of delete command you can delete all rows. If you skip the wear
[03:03:03] clause you can delete all rows that okay and whereas truncate always going for all rows deletion. Trunket is going for all rows deletion that is and you have a any important rows has been deleted with
[03:03:18] the help of roll back command. But in a truncate it is not permanently rows will be deleted. If you delete all rows from table with the help of truncate command truncate command that rows you cannot get back. That rows you cannot get back
[03:03:33] here. That is the point you have to be remembered. That's about truncate one that is one here what my truncate command is there here you no need to practice these things my here but because the data what it is there I
[03:03:49] required that for next coming examples that is so here you go for the list of that is so here you go for the list of what that is a trunate what that is truncate command is there so I hope that these all commands are
[03:04:05] complete Right? No need to practice drop and truncate. Just remember that is whenever it is required then you can go.
[03:04:19] I hope that the difference between drop delete to truncate. Understood or not? Drop command will delete entire table. But other objects
[03:04:38] truncate and delete is here. Now truncate to delete both are meant for deleting rows only. Truncate will delete all rows from table which you cannot get back those rows. Whereas delete command also deletes all rows or specific number
[03:04:52] of rows based on the condition and you have a chance to get back those rows. in order to discuss no roll back we'll discuss just wait for navindra okay okay commit to roll back commands has to discuss combinationally just wait for
[03:05:07] that I'll explain it okay first you just follow these all things what I'm explaining right now this is then so these are the just differences between drop delete truncate
[03:05:28] some other command called selectma. So because select command how to retrieve data from table and all that we'll go for it right now. Let's see that is how for it right now. Let's see that is how it works. Okay. The next command is guys
[03:05:41] here select command has to go for it here. Now what is that select command guys? First of all one thing uh just to discuss about the select command we need
[03:05:53] discuss about the select command we need proper data. We need proper data that is so as I said beginning of today's session okay everyone has been downloaded that SQL data sets right downloaded now
[03:06:09] SQL data sets right downloaded now got it in this SQL data sets in this SQL got it in this SQL data sets in this SQL data sets ma go to the assisted projects data sets ma go to the assisted projects and so check that this emp table is
[03:06:21] there or not here it will be there that see There when you open SQL data sets, assisted practice data sets are there. There you have lesson five first lesson
[03:06:33] and you have here EMP table is there. It is the it is not Excel actually it is CSV file. When you double click on it when you double click on it you see there here you can find some sample data.
[03:06:49] Now this data we wanted to bring it on to our database
[03:07:01] okay this is now this data we want it right now so what I'm doing right now you know here uh what you say that is so this is what uh what you say that is so this is what type of file in the sensma it is CSV
[03:07:14] file you just see there that is so see there Microsoft excel comma separated values file that is CSV but it supports Excel so that we are able to open on Excel that now this table is there some data that data we
[03:07:30] table is there some data that data we wanted to importing to our database from CSV file to here from CSV file to here we wanted to import that so now I'm performing one small task
[03:07:46] just follow everyone and try to do it that you have to guys. So now I want the table. Go to the So now I want the table. Go to the tables guys. See there. Go to the tables
[03:07:58] and right click on tables. Right click on tables. You will get an Right click on tables. You will get an option called table data import wizard. Table data import vizard is there. You click on this.
[03:08:13] So you'll get here. Now go to the browser on the browser on the browse go to the where that file is there. So in my system it is in simply learn and SQL 26
[03:08:30] simply learn and SQL 26 and SQL data sets assisted data sets. Go is selecting the DMP table and go for open it. Yes, you'll get like this.
[03:08:45] Yes, you'll get like this. Then go to the next. So here it will ask you that. So table name, what is the table name? Just keep it the table name as EMP only. The file name will be taken here. But you go to
[03:09:00] name will be taken here. But you go to empore
[03:09:12] columns and its data everything it is showing to you here now the data is coming right now there then next next okay simply go for next and next next go
[03:09:27] for finish that's it ma the table will have here just see that go for refresh see you have a EMP table. have a EMP table. You have a EMP table that separate file
[03:09:41] you took. And you see there guys, right now select star from EM. We imported data from CSV file into our database. See that we got the data
[03:09:54] database. See that we got the data entire data. We got it here. because select statement has got a different clauses so that some proper
[03:10:08] data should have in a table so that I took this table from simply learn only took this table from simply learn only that can you do it that right now
[03:10:21] guys do you want me to show once again or else will you do it
[03:10:39] on the tables of your database and go to the table data import vizard. the table data import vizard. Table data import vizard. And here it will be empty actually. go to the browse and where that file is there that you go
[03:10:53] and where that file is there that you go to. So SQL data sets assisted practice data sets lesson five and select that and go for open it will come here. So then once it come here now go to the next and here you change the table name
[03:11:10] table name changing remove that underscore table and go for next next underscore table and go for next next next simply next next until finish that's it because I already did know because of that I'm not continuing that
[03:11:25] because of that I'm not continuing that yeah so I hope that everyone has imported that EMP table, right?
[03:11:38] Everyone imported EMP table from CSV file to your database.
[03:11:51] this command. The select command is the thing here. The select command is the thing here. Now, select command is the here. So what is a select command and how to work with the select command because it
[03:12:06] has different clauses and all that is there. Let's go for one by one here. So there. Let's go for one by one here. So [snorts] how we can go for that is so first of all the purpose of select command is here to retrieve data from
[03:12:21] single or multiple tables. We already working on uh previously also. Let me select start from table name or something that the completely how you get that is you see there here see that this command is used to retrieve data
[03:12:37] from single or multiple tables that is one thing when it coming to that here see this is the syntax of a select statement you have to follow. See these are the different clauses we have to discuss now.
[03:12:52] So select and here expression should be there is some expression you'll come to know that from table name that is this is mandatory part and the clauses what we have here these
[03:13:08] are the different clauses we have where clause group by clause having clause order by clause something you have that is okay this is one thing so now here when it going for that see that one by one So to discuss these all clauses we
[03:13:25] have taken one table that is employee table in this employee table guys you see the data so first of all we'll talk about this employee table how we observed that table data here employee ID is there
[03:13:41] table data here employee ID is there first name last name gender then role is one we are having department is one we are having experience is there, country is there, continent is there, salary column, employee rating, manager ID like
[03:14:00] you are having. So you see here employee E260 is the employee ID first name Roy last name Collins gender a male gender senior data
[03:14:13] scientist working in a department of retail he is having 7 years of experience living in India continent of Asia Asia salary is 7,000 let's say that EMP rating is three and what about this manager ID what about this manager ID
[03:14:31] manager ID what about this manager ID this is very important Right now E E583 that means this entire table structure is a hierarchical process. Every employee has got their their next level employee reporting manager.
[03:14:45] Every employee has got their next level employee managing reporting person. So that is what is this E583? It is also employee number. With this employee number here you have to search who is that employee. 583. Yes. See there. So
[03:15:01] that employee. 583. Yes. See there. So Janet Halle female can she's a manager. Janet Halle female can she's a manager. Manager for what? Retail department. See so Joy Collings is an employee of senior scientist working in retail department.
[03:15:15] For the department Janet Hal is the manager. Okay. 14 years of experience living in Colombia South America taking 10,000 salary. Okay. Now employee rating is two. But she is the manager but she's
[03:15:31] also having next reporting manager. Who is that E002? Who is that E002? You have to search that E002 here. Yes. Cynthia Brooks
[03:15:43] female president department is all in one that 17 years of experience Canada one that 17 years of experience Canada North America 14,500 is taking rating is She president also has to report to someone. Who is that? E001. Who is E001?
[03:16:00] someone. Who is that? E001. Who is E001? Arthur black male CEO of the company. Okay. 20 years of experience USA and North America. So CEO is not going to report any other person. Himself is a reporting person. That is one thing.
[03:16:15] Okay. So like that who is reporting to whom? Who is working under whom? That is what we are having. So first employee ID column last manager ID column same data
[03:16:28] but in employee ID unique numbers will be there in manager ID repeated numbers will be there that is what we are having. So this is the structure of data here now. So from this data we'll make some queries as of
[03:16:42] this data we'll make some queries as of now. So select star from EM. Can you tell anyone that what is meant by star here? What a star represents ma what the star represents
[03:16:57] somebody having some already knowledge know for those people I'm asking that know for those people I'm asking that h it's not a complete data all columns h it's not a complete data all columns are correct all columns
[03:17:10] column column representations that is all columns column representations that all columns column representations that is so all columns you can go for that
[03:17:26] Okay, that is you find that for example let's see that is sir I don't every time I don't want to see all the columns data so uh
[03:17:38] sometimes I want only specific columns data here then how to write a query in the sense of in the place of star you define the column names that's it define the column names over there so how you go for that is here select
[03:17:55] So I want first name so that first underscore name underscore name then comma so then job is there ro then dt okay then salary so from EM only those
[03:18:11] four columns I want only those four columns data we can get it that see table having all the columns but right now only visibility is four columns
[03:18:23] right now visibility is what that Four columns visibility we are having that is. So that is one thing just we are getting So that is one thing just we are getting right now like any number of columns any
[03:18:35] sorry any columns which you can take down that that is not an issue here see down that that is not an issue here see all table that is now here we can perform some calculations over there here now so calculations
[03:18:51] so how the calculations will go in the sense so for example so let's see one query calculate uh yearly sal yearly salary of all
[03:19:04] employees. Suppose if you go for like that salary is one column which shows you monthly salary. For that I want yearly salary for that
[03:19:16] what I want here. Now yearly salary I want it there. Then how that yearly salary should go here. Okay. So now see there Okay. So now see there selective so first underscore name I'm
[03:19:31] selective so first underscore name I'm taking only first name that so then ro then salary so that is fine here then here you can so that is fine here then here you can write like this salary into 12
[03:19:45] doing some multiplication there so then from emp you can take that is see there 7,000 is the salary monthly salary that
[03:19:59] is and salary into 12 in the sense 84,000 is there now like you have now all the employees salaries that is monthly salary and yearly salary is there but this column is not present remember that
[03:20:15] this last one is not present on the table don't think that it will be present on the table no for temporarily selected statement is generated that column for showing the result. That's it. We're showing the result here. But
[03:20:29] in a table only you have a salary column. No other column will exist there. Here except the physical all columns that okay that is one thing. But here one point is there. Now
[03:20:43] whatever the formula you have written here that formula has been taken as a heading of the column. heading see that here we got it that one but I don't want
[03:20:55] like that what I want in the sense of I want there a heading yearly sal something I want that okay how that process can take here so okay how that process can take here so that we are having here column aliases
[03:21:11] that we are having here column aliases what is that column aliasis is there what is that column aliasis is there okay say this is what column alias so it it is an alternative name for the table columns or expressions of select
[03:21:24] statement only. Remember that how you go for that is here. So select how you go for that is here. So select specify the column name or expression for that one as alias name has to specify it
[03:21:39] alias name has to specified that is see how you write a query for that mark see there now I'm taking right now there selector selector first name as emp name see I'm changing
[03:21:54] first name as emp name see I'm changing the name of that ro as So designation the name of that ro as So designation then salary as a monthly
[03:22:07] sal. So then salary into 12 as a yearly sal then here salary by 30 as of day wise sal
[03:22:24] by 30 as of day wise sal okay from emp a emp name role has been taken as a designation salary has been taken as a
[03:22:37] monthly cell physical column names remaining two are the calculations I did that and I'm going for that see there now now see there this is 7,000 is the see there
[03:22:51] first of all see the heading employee name emp name designation monthly sal yearly sal day wise sal something like you are having that
[03:23:04] changing the column names physically in a table physically in a table column go for alter command we already discussed that here temporarily for a select statement as the queries whatever the headings we need that we are getting
[03:23:19] the headings we need that we are getting that's called as an alias column aliasis what is that column aliasis you can write down here so this is one thing so guys here first what we're taking getting all columns
[03:23:35] listen carefully that the second part is right now it's getting required columns whatever you want the specified columns okay whatever it is there then if you want to perform any calculations that just see there the calculations you can
[03:23:50] perform it like this this is what along with the physical columns then by using column aliases for the calculations if you want headings or something then you can go for here that is first name as EMP name role as a designation salary as
[03:24:07] a monthly sales something like this. So this is the one just we do then So this is the one just we do then simple one part of expression section. So then here one more also there guys right now there is a clause called
[03:24:24] distinct clause is there. So there is a clause called distinct clause. What is this clause? In the sense this clause shows only actual or unique values of specified column by avoiding duplicates.
[03:24:39] What it means in the sense map you just see there. see there. For example, if you take some columns For example, if you take some columns now see there now you tell me how many
[03:24:52] job roles are there here. Now how many job roles are there? So senior data scientist is one job role. Junior data scientist is another job role. Associate data scientist is another job role. Senior data scientist
[03:25:09] sorry like four is there right now. Okay. Senior junior associate. Let's see some other one here. Lead data scientist four.
[03:25:23] Lead data scientist four. Manager is five. President is six. COE Manager is five. President is six. COE is 7 like you have that and even if you come to the department column you have a deep uh retail
[03:25:36] department one department finance is another department automotive is another department yes that's it four departments are there all five departments and even when you come to the here now
[03:25:50] country you have India China Colombia USA Germany France Canada. Okay. Like this you are having that is in this way. Suppose if you come to the gender only two values male
[03:26:05] female. I wanted to see that is now tell me see there guys here select DP from EMP. If I write that what is the output of this query? Ma
[03:26:20] select DP from EMP. In the sense what is the output of this query?
[03:26:34] What is the output of this query? All values of that d column will come. Right? Only one column dpp will come. All 20 values will come. Right? All 20 values will come. Right? All 20 values will come there. See
[03:26:49] All 20 values will come there. See there. Let me execute that. is okay this is one but I don't want that
[03:27:01] okay this is one but I don't want that duplicates I want actual values very duplicates I want actual values very simple thing that just add a distinct simple thing that just add a distinct dbd from emp
[03:27:13] now now what will happen you know only actual values will come see it eliminates all the uh duplicates and shows actual values only for showing prospection in a table it is there we are not disturbing anything table only
[03:27:28] retrieval prospection see there now five departments we have like you have other also suppose like a distinct gender
[03:27:46] group in the sense what chendra I'm not getting your point I'm not getting your point gender spelling mistake. gender spelling mistake. Yeah.
[03:28:08] that. You wait for group I clause in order to start a discussion. No, just wait for that. That is okay. Okay. We are just staying on distinct clause here. So like this. So we are having uh like this. But that query is correct.
[03:28:24] That query is correct that. So then here now country from EM actual countries will be showing from EM actual countries will be showing to us here. Now see
[03:28:38] like you can take down guys. I hope this distant clause is guys. I hope this distant clause is understood by everyone. Right? understood by everyone. Right? Right now is it clear or not?
[03:28:51] like you have some guys here just let me continue the explaining this anyhow you have a table ready so now this queries you can
[03:29:03] practice it I'll give some time after discussing about some part of this one okay this is so this is what one section of select
[03:29:16] statement what is that section here select clause this section is completed. select clause this section is completed. So in this expression area whatma in this expression area we got the three sections there. One is all
[03:29:34] columns or specific columns that is the first one all columns in the sense star specific columns in the sense taking only that column names. The second one is performing calculations. While doing calculations if you want we can give
[03:29:48] alias names that is second one. Third one is providing distinct clause to see actual values from specific columns. Okay that is one thing we are having.
[03:30:00] These are the things we should go. Next. Now another clause is there where clause. So where clause we already discussed for the previous two commands discussed for the previous two commands for update and delete. In update command
[03:30:15] where clause used to modify the specific row values based on condition. Delete command also having deleting rows based on the condition. Now retrieve the rows based on the condition. Retrieve the rows based on the condition
[03:30:30] that is. So that is what here where clause. So it is used what method is used to specify conditions on table column to retrieve rows. to retrieve rows that is how it should
[03:30:46] go. Select blah blah that is one comma here where column name operator value here where column name operator value has to specify.
[03:30:59] come down to here. So there are some examples on clause. So what is that? The very first one show all employees
[03:31:11] working in working in uh what is that uh India working in uh what is that uh India country guys? One second, guys.
[03:31:52] see there show all employees working in India India so working in India this is the one now so working in India this is the one now so usually we go for that EMP that is
[03:32:09] column is there in this country column we go for like this where country is equal to India has to single
[03:32:22] quotation is must because of stringle see that where class only three see that where class only three employees are working in India three employees are working in India that is
[03:32:36] okay now you tell me that show all male employees show all male employees male employees show all male employees give me the query for This
[03:32:50] show all male employees. Give me the query guys.
[03:33:17] the answer are given that is correct there
[03:33:32] your query is correct if in a in a gender column you have male female gender column you have male female options it is y that two you should not use the double quotes you should use only single quotes
[03:33:45] you should use only single quotes in double quotes concept is not there in a double quotes concept is not there in a MySQL that means in a database systems only single quotations here requir
[03:33:59] requir mail okay M only
[03:34:15] like that any conditions you can write on here. Suppose show all employees on here. Suppose show all employees in uh like show all managers.
[03:34:27] in uh like show all managers. Show all managers. This is one more you Show all managers. This is one more you can take down. Okay. Then show all uh can take down. Okay. Then show all uh like employees. So in uh what we can
[03:34:41] call that retail department retail department that is one more okay this is one thing these are the simple conditions that is guys here now then
[03:34:53] conditions that is guys here now then show all employees so whose show all employees so whose experience is so greater than or equal experience is so greater than or equal to 8 whose experience is greater than or
[03:35:06] equal to date like this. Now see there now we have given conditions on only one column. All these queries are having one column conditions. Suppose if you have
[03:35:18] the multiple column conditions then how can we go for that is let's say now here can we go for that is let's say now here uh show all employees whose sal is greater than or equals to 8,000 and
[03:35:37] is greater than or equals to 8,000 and less than or equal to 10,000 so I want to go for like this so one here see the query can get like this here now select to start from EMP where see that guys salary greater
[03:35:53] [clears throat] than or equal to 8,000 and salary less than or equal to 10,000 you must write like this only okay this is let's see how many employees are
[03:36:06] is let's see how many employees are there whose salary between 8 to 10,000 yes we have a like five to five employees are there here let's see their salaries here. So 9,85 9,500 10,85 is there.
[03:36:22] Fine. That is in between 8 to 10 only out of 20 employees. But guys you see there here two conditions we mentioned two
[03:36:34] conditions we mentioned but those two conditions on same column that range of conditions on same column that range of values in this case. So we have an values in this case. So we have an operator is there between is there. The
[03:36:48] same query we can write using between how you know that select start from EM how you know that select start from EM where salary between
[03:37:00] where salary between 8,000 and 10,000. This is also give the 8,000 and 10,000. This is also give the same answer. See there. Now this is also same answer. See there. Now this is also gives you the same answer that see there
[03:37:13] gives you the same answer that see there same answer has been given to us using same answer has been given to us using between here. Now anyway we can use but the second one will give concise the query.
[03:37:29] Yeah, it includes it includes 8,000 as well as 10,000 also including 8 and well as 10,000 also including 8 and 10,000 between operator.
[03:37:43] 85 is there 8,000 salary no employees are there. Okay. You do one thing that you you can check that see already 10,000 is included 10,000 is included like that 8,000 also will be included.
[03:38:03] and one more is there here. Let's see that guys. Uh yes, one more query here. that guys. Uh yes, one more query here. Show all employees uh
[03:38:15] in uh retail [clears throat] and automotive and automotive what automotive departments from those two departments we need to get employees here. So then how can we
[03:38:31] write that you know here? So then we can go for here now where DBT So then we can go for here now where DBT is equals to retail is equals to retail R DBT is equal to automotive.
[03:38:47] You need to write like this here again one column with R operator. So that is and in the same in same column in same here both retail and
[03:38:59] automotive it will not be there. Either it should have automotive or else it should have retail or else it should have healthcare like that I'm getting have healthcare like that I'm getting two you see there here select that and
[03:39:11] go for executing that is right now 11 employees are there in from those two departments 11 employees are there here
[03:39:25] trying to explain here if you keep the under see what happens There under see what happens There no rows will come
[03:39:39] now we are getting that here. See there. So on department column see and in the sense what happened you know suppose it is checking this value there it will search for retail as well
[03:39:55] as automotive both values you will not find on that particular cell no and in the sense that is the meaning under or in the sense what either it is retail
[03:40:07] or else automotive any one condition is true true you got my point wa Right.
[03:40:21] Want is it clear? Yeah. So now here also R operator is there and one column multiple values. This you can use it in operator. There
[03:40:33] is an operator called in is there. So this kind of queries you can minimize this kind of queries you can minimize like this. What is that? You know in of like this. What is that? You know in of like this.
[03:40:59] will be getting. See retail and automotive employees are there. column because condition is on department column. No, there it checks
[03:41:11] department column. No, there it checks here. is so this is the one we are have.
[03:41:24] so this is the one we are have. Okay. So now we'll go for right now Okay. So now we'll go for right now multiple conditions on multiple columns. Let's see that multiple conditions on multiple columns. For example guys, you
[03:41:37] see here show all male managers. See if I ask like this a question show all male managers. I want all managers
[03:41:49] all male managers. I want all managers who are whose gender is male. Then how can we go for that is see that guys select something like here where
[03:42:02] gender equal to y so that all male people male employees so that all male people male employees will come from that one manager only managers I want that means what here role column is has to be taken and
[03:42:19] role column is has to be taken and role equal to manager Is it understood guys? Only male managers we go for here now. Two members are there. What? But that is
[03:42:34] Petty Alen and Patrick Wols is there. Now only two employees are there. Two Now only two employees are there. Two manager male managers are there. manager male managers are there. See guys try to understand
[03:42:47] salary column between operator two conditions. Department column R operator that is in. That means previous two examples multiple conditions on same column only we did right now multiple conditions on a different columns. Now
[03:43:03] one condition on gender one condition on male. male. So that is one thing we are having here. So that is one thing we are having here. Okay. So then one more query suppose
[03:43:15] here see show all female show all female employees show all female employees whose salary between whose salary between uh so
[03:43:30] between whose salary between uh so something like 5,000 to 10,000 yes guys will you write the answer for this this Will you write the answer for this guys?
[03:43:47] I'll write the answer for this one first. Will you try for that?
[03:44:55] M gender equal to MF and salary between 5,000 and 10,000.
[03:45:17] Yes, ma'am. Gender and salary. So, are all answers are correct only that I'm receiving.
[03:45:37] See there now here gender is F4 and we can write down that salary between what that is 5,000 and 10,000 we should go for like this
[03:45:49] here that see all female employees see their see all female employees see their salaries 75 77 55 10,000 and 85 is there
[03:46:01] salaries 75 77 55 10,000 and 85 is there that. go for it right now. There. Yeah. Shall we go for one more? M here.
[03:46:14] See show all employees. So whose department employees. So whose department either what is retail
[03:46:26] either what is retail or automotive salaries between uh what method is 5k to 10k.
[03:46:41] between uh what method is 5k to 10k. Now can you make the query for this? Can you make the query for this here that values you can replace it to either any other values like 5k to 10k or 10k to 15k or something else you can make it
[03:46:54] that that's not a problem so what is the answer for this ma what I'm writing there so this is the correct answer I'm so this is the correct answer I'm writing now okay select
[03:47:08] so something like we are having where dbt in dbt in what that is retail
[03:47:23] or automotive. Okay. And salary or my salary between 5,000 to that means under 10,000
[03:47:35] 5,000 to that means under 10,000 that will be there to you right now. that will be there to you right now. Yes ma'am.
[03:47:54] Now let's see that is here. Now show all employees. So whose experience between uh like what we can say that 5 to 10
[03:48:06] years. Now write the query for this. just I'm seeing the data and writing that questions in front of you that to
[03:48:19] make you practice to make you practice that is
[03:48:51] Yes, that's it. That is very simple query that between operator. I'll do one thing. See, show all employees. No. So then five years
[03:49:03] employees. No. So then five years show only first name only first name and uh experience column and experience columns. Then if I write like this what is the
[03:49:19] Then if I write like this what is the answer for this? Now you tell me I want only those two columns. I want I don't want all columns that
[03:49:41] yes in the place of star you'll get that is right up what that is select first name comma
[03:49:54] experience from EMP where experience between from EMP where experience between five and 10 we are going to write it
[03:50:06] down over there how many employees are there six how many employees are there six employees are there see 7 years 6 years 8 years 9 years something like we are having that is
[03:50:35] all senior data scientists senior data scientists what mother is senior data scientist. So any spelling method is scientist from
[03:50:49] any spelling method is scientist from retail department. Uh right on now retail department. Uh right on now show all senior data scientist. Can I hear that is use only
[03:51:04] first name first name, first name, uh what that is job and department uh what that is job and department columns.
[03:51:21] Yeah, department job in the sense here it is role the visibility only those three columns here
[03:52:51] queries. Remember that not only the answers that means if anyone is facing any issues that please let me know. Don't be sit idal.
[03:53:06] getting any right uh issues while writing the queries please you know write a query here I'll help you
[03:53:22] I can't anything because I can't predict you know who is writing who is not you know who is writing who is not writing that.
[03:53:38] See there I have written over there. queries here now. That means you got some exposure writing the queries and
[03:53:50] multiple conditions and all that is still we have some more questions are there. But okay let me go per other topic. topic. So in this where clause only we have now
[03:54:03] uh one more is there an operator called like operator is there this is everything is okay here it is not there okay fine that operator is there what is that like
[03:54:18] operator is there what is that like operator how to add names of manager in the row names of manager in the row next to ids
[03:54:35] >> that means you want employee ids right you're asking about employee ids chendra you're asking about employee ids chendra so that means employee ID and uh
[03:54:52] that means you you are asking like who who is whose manager right who is whose manager that is Yeah, for that we have to write a self join self join has to be right that is joins
[03:55:05] concept I'll give the answer for this who is whose manager that one okay self join query is there now I'll show you there yeah now here there is an operator called
[03:55:21] what is that you know that is a like operator is there here now like operator So this is used to specify
[03:55:33] So this is used to specify condition pattern based conditions pattern based conditions on string and date values date column
[03:55:46] date columns columns that we are having. This is one thing. So here two uh symbols So here two uh symbols has to be used. What is that? You know
[03:56:02] the very first one is like a percentage. So it ignores group of characters. Group of characters that then another one is underscore.
[03:56:17] Okay. Ignores single character. single character like this. What is this? You know how this can be used in the sense? For example, if I ask a question like this, show all employees
[03:56:35] question like this, show all employees whose first name starts with the an alphabet yes. For example, let's say that is that is first name first name first uh first character should be yes.
[03:56:52] Remaining all anything remaining all anything here I want to go for like that then how you write that is how you write that one here so for this kind of situation sir you are having a like this
[03:57:11] situation sir you are having a like this what is that you know like select something like I'm writing that where first name like You should not use the first name like You should not use the equals to. You should use like in a
[03:57:26] single quotes. First letter is what? Yes. And remaining all characters or anything. So that should be ignore. Ignoring of remaining characters. So that right now the percentage symbol can use it here.
[03:57:41] use it here. Percentage symbol can use it. Okay. Let's see that any employees are there here with the starts with yes. Yes, only one employee is there. See there Steve.
[03:57:55] Steve is there. Let me check first of all list of employees. So you will get some couple of data over there. Yeah. of data over there. Yeah. Uh so this query you make it with K.
[03:58:11] is so whenever you go for this one K will come. That is let's see that first name with K. Yes, two employees are there. with K. Yes, two employees are there. Katrina Karina is there. Now see in the
[03:58:25] first name only see in the first name. So like this sir if we put here equal to what happened equal to in the sense it
[03:58:37] will search for an employee with K percentage. An employee with K percentage employee name with K percentage. It's not like that in employee in a employee first name first letter should be K remaining all
[03:58:52] anything that grouping will be taken care by like now let's see that one more that guys here what is that one first name ends
[03:59:04] here what is that one first name ends with what is ends with for example let's with what is ends with for example let's say that some a last letter is a so percentage a should write here now percentage A should write it over there.
[03:59:20] Yeah. Katrina, Diana, Cynthia just see there three employees are there. All the there three employees are there. All the employees first letter is A. All the employees first last letter is A.
[03:59:34] So something we get that is and one more also you can go with that uh what is that? Show all employees whose uh first name exist. What mother is exist
[03:59:48] name exist. What mother is exist alphabet A? Exist. Exist in the sense now anywhere exists in the sense whether in first position in last position in the middle anywhere if you go on that then
[04:00:00] anywhere if you go on that then percentage a percentage will go. Okay, that is then go for clicking on this one. Almost 13 employees are there. 20 out of 20 employees, 13 employees names are having alphabet A in their
[04:00:16] names. It might be in a different position. See Nion is there. Katran, position. See Nion is there. Katran, Karan, Williams, David, Diana, Ched, CL, Patrick. See everywhere you have alphabet A. See Arthur first letter is
[04:00:31] A. Cynthia last letter is A and remaining all having some in the middle. So like this we can go this percentage symbol that depends but actually here if
[04:00:45] you have in this table date column we will have some examples on dates also but no problem we'll generate another table there we'll have that. Then when we use that underscore when we use that underscore symbol the
[04:01:01] underscore symbol will be used like this uh underscore in the sense what ignoring uh underscore in the sense what ignoring single character ignoring what that is single character suppose same thing that let's see that is here I'm writing here
[04:01:18] now show all employees whose first names second letter starts with let's say that Second letter starts with a for example let's say that second letter that means first letter might be anything first start letter anything that then
[04:01:36] here we can go for first you have to make one underscore then alphabet a then percentage so first letter is one underscore one letter then a then remaining all characters should ignore
[04:01:51] characters should ignore that you can take down Here see that stick the second letter of every employee here. So Janet, Patrick, David, Karani, Katrina all second letters are having a
[04:02:06] suppose if you want the third letter is a two underscores. Two underscores will be there. So like that we can take down. that we can take down. So then one more query you can get like
[04:02:21] name length is five characters.
[04:02:34] So if you have like this I want some employees whose name having only five employees whose name having only five letters whatever it is the letters then letters whatever it is the letters then five underscores has to be used 1 2 3 4
[04:02:46] 5 underscores. letters. David five letters. Janet P letters. Emily P letters. Tracy five
[04:03:00] letters. Emily P letters. Tracy five letters.
[04:03:32] one more I'm just giving that uh like first letter I made it like uh like first letter I made it like here. Now uh yes see first letter is yes sir and remaining four letters I have given ignoring yes 1ore 23 4 now tell me
[04:03:51] given ignoring yes 1ore 23 4 now tell me what is the result of this query
[04:04:14] anything first letter is yes is okay. So we used underscore over there that means total length of the name is five letters within that first letter should be yes only
[04:04:29] that is the meaning okay that means so you'll get only Steve this time Steve only you'll get but the question goes like this here now
[04:04:45] so length should be five characters and uh name should starts with name should starts with what mother is alphabet. Yes, you should go for like this. Then this
[04:05:01] is the answer for that like you have some queries on this one here. This is what just we are having with the like operator that is like with percentage and underscores. How can we
[04:05:16] percentage and underscores. How can we use it? These all here use it? These all here like this we can go for different uh uh what we can call that is so queries on where clause what my is where
[04:05:32] queries on where clause what my is where clause part we are having this is okay this is one thing just we go so I'll write I'll give one question just write on that here uh
[04:05:46] just write on that here uh So, show all employees. So, whose name? Whose first name? Whose first name? Second letter
[04:05:58] Whose first name? Second letter is A. And fourth letter is A. And fourth letter is is again something E. Let's assume like that.
[04:06:15] anything. Uh E fourth letter is anything. Rest of letters letters ignore it. Now write the query for this.
[04:06:41] What should how to select employees first? which has a employees which has a as the last character in
[04:07:12] that uh underscore for the first not percentage no how you go for percentage underscore should go
[04:07:26] swag your query will check only four letters which is having second letter E and fourth letter E you should keep the
[04:07:39] fourth letter E you should keep the percentage symbol after E
[04:07:56] A_E then percentage should go I don't know how many employees are will come yeah we have a two K and Janet
[04:08:12] that will give us here now only four letters will be considered pachchi but after but the length of this name has been not defined.
[04:08:24] You have to see that two lines of question rest of letters ignore that is what actual question complete
[04:08:37] question is that is here so that's about some of the examples of where clause so that is what as for the syntax now where clause is completed where clause is completed now combine come to this group by clause
[04:08:52] but one thing guys whenever you Come now we have to discuss about these two group we have to discuss about these two group by clause and having clause has to go. So for that first of all you have to work on group functions to work with the
[04:09:05] group by clause. Group by clause cannot be used directly here. Group by clause be used directly here. Group by clause need some predefined functions called group functions. We'll call it as what that is group
[04:09:19] function should. Okay. So what are that group functions Okay. So what are that group functions and how can we use those group functions
[04:09:41] yeah proy I'll speak with you at the end of session so these group functions for the this these functions are used to perform
[04:09:55] different operations. Different operations on the numeric columns of tables. Numeric columns only only numeric columns that will go. Okay. Here. So the functions are like
[04:10:09] Okay. Here. So the functions are like max, min, sum, average, count, count star, something is there. Let's see now how these functions will work first. So whenever you come down here
[04:10:24] So whenever you come down here so that here now yeah so that here now yeah select I'm writing sorry max of salary from EMP if you go on taking like this guys
[04:10:39] listen if you go on taking like this that will give you maximum salary of from all employees finding a maximum value from the salary column that's it value from the salary column that's it see 16,500 is the maximum salary
[04:11:05] so let's see that is right now there what that is find max
[04:11:17] now here uh find max comma min comma Uh sum cell. Okay. Then average sal average sal. Okay. from all employees.
[04:11:32] Then how you go for that is you can go for like this guys to find the maximum for like this guys to find the maximum salary max of sal is there salary then minimum salary in the sense min function is there minimum salary
[04:11:49] then total all 20 employees how much of salary they are taking there is a function called sum added that salary so then here on an average how much of salary they Every employee have AVG
[04:12:05] salary from EM. That's it. Here see that the maximum salary is 16,500.
[04:12:19] The minimum salary is 2,800. Totally they are taking 1 lak 56,800 salary. And on an average they are taking 7,815 rupees every employee.
[04:12:34] taking 7,815 rupees every employee. Okay. So like this we can so with this functions this is only we can do it here. And one more see there here suppose count the
[04:12:52] suppose count the um count all employees. So in a table um count all employees. So in a table in a table that how you go for that is select count of star
[04:13:07] count of star from M. So how many rows are there? that is 20 20 rows 20 employees that every row
[04:13:19] 20 20 rows 20 employees that every row having one employee 20 rows 20 employees and at the same time if you want you can count if you want you can count number of values in a column
[04:13:36] count number of values exist in for example number of values exist in for example let's say that first name column Then in this case you have to go for like this counter first name
[04:13:52] from emp you'll get 20 only because in a table guys listen that in a table we are not
[04:14:06] guys listen that in a table we are not having anywhere null values. We are not having anywhere null values. See there in a table complete data is filled at any moment any null values are there. Null values so will not be
[04:14:23] counted here. Null values will not be counted over there. That is one thing you have to remember. That is one thing you have to be remembered here. Now null values will
[04:14:38] remembered here. Now null values will not be counted. Let me write on the note what mother is null values will not be counted.
[04:14:51] will not be counted. So that is one thing you have to only functions that is okay but one more point you have to remember here what is
[04:15:06] point you have to remember here what is that you know you may find uh you may get an idea sir it is showing maximum salary okay 16,500 can I get employee name of that maximum
[04:15:20] salary three can I get that in the sense of course we can get but uh not in this way for example and as usual suppose if I write first underscore name and if I write this one you see there
[04:15:36] what will happen will I get will we get that name see showing an error in aggregated query without group by clause cannot be used. That means guys
[04:15:53] clause cannot be used. That means guys here no column should be used directly no column should be used directly if you're not using the group by clause.
[04:16:05] So that functions what you are using max mean and all directly you have to use like this only directly you have to use like this only here this is the one we are having okay fine
[04:16:20] this is the one we are having okay fine that so now this functions is so how these functions utilize in a different ways in the sense based on these functions we have a clause is there group by clause clause.
[04:16:36] Group by clause is there. What is that group by clause? See that this clause is group by clause? See that this clause is used to divide a column into group subgroups. Subgroups based on other columns based
[04:16:51] Subgroups based on other columns based on other columns then allows you perform on other columns then allows you perform group functions. what is that you know. So how the group functions? So group by clause can use in
[04:17:06] functions? So group by clause can use in the sense show max salaries. See dma the sense show max salaries. See dma here. Now show max salaries based on here. Now show max salaries based on each department. I write like this.
[04:17:21] So select max of salary from EM in the sense from all the employees maximum sense from all the employees maximum salary showing that. Now I wanted to show Now I wanted to show maximum salaries on each department.
[04:17:35] Now I wanted to show maximum salaries on each department. Then how that can be each department. Then how that can be taken up. Yes. See there. Now select max of salary from EMP.
[04:17:51] So here you have to write down group by what is that? D PD H what is that? D PD H we can use like this
[04:18:03] see there but we got maximum salaries but 10,000 is the maximum salary for which group sorry which department so this time whatever the listen guys whatever the
[04:18:18] column name you use in group by clause whatever the column name you use in group by clause that column name you can mention here.
[04:18:31] Yes, like this we can do. Now see that h retail department maximum salary is 10,000 finance department maximum salary is
[04:18:46] 10,500 automotive is 11,000 healthcare 9,500 automotive is 11,000 healthcare 9,500 all is 16,500.
[04:19:45] like this. What is that? Take this one here.
[04:19:58] each let's make it country wise that is a wise then can we make it like this here? Now then can we make it like this here? Now you go for in this way country
[04:20:12] then what should we write that? Yeah Yeah group by country.
[04:20:30] India the maximum salary taking is right now 8,500 minimum is 3,000 totally now 8,500 minimum is 3,000 totally 18,500 is there 6,166 is the average salary like we have a China okay now only one employee is there I think so so
[04:20:46] that maximum Colombia Colombia okay USA that is Germany and France that okay USA that is Germany and France that Canada
[04:21:26] So what is that you know counter number of employees in each job role each job role that is
[04:21:40] count the number of employees in the job role. role. Okay. So how that process can take here?
[04:22:04] from emp group by what that is group by ro can go group by what that is group by ro can go for it.
[04:22:23] having five members. Junior data scientists are having three two members like you see there. Now manager is five five managers are there.
[04:22:35] One president one CEO something like you are having this is okay. So this is the one just you can write down there. Okay. So this is the way we go for
[04:22:51] working on the group functions and the group by clause. Listen carefully guys. Group by clause is completely depends on the group functions
[04:23:03] individually also we can use it but most of the time group by clause can take on group functions only. on group functions of Chandra Praash. I think so you have given one query like this. What is that
[04:23:17] given one query like this. What is that gender? Okay. Now from EM gender? Okay. Now from EM group by gender
[04:23:32] see that two groups the distinct clause also we did that.
[04:23:44] but not frequently on this kind of columns. Group by clause we'll go for always. So maximum 90% cases on group functions only we go for that is
[04:23:56] functions only we go for that is yeah guys. So then one more is there yeah guys. So then one more is there here what is that you know here what is that you know uh having clause is there guys.
[04:24:08] One more is there. What here? Now having clause is there. clause is there. Okay. Generally, what is the purpose of Okay. Generally, what is the purpose of uh this one? Where clause?
[04:24:20] uh this one? Where clause? What is the purpose of wear clause guys? purpose of wear clause? Why we are using where clause
[04:24:33] to by specifying conditions we can retrieve specific rows. Yeah. Filtering prospection. Yeah. So now you tell me you are we are making conditions right? We are making conditions on what columns.
[04:24:48] We are making conditions on table columns. Okay that is here there is a clause is there. Having clause listen there. here. Now having clause is there. This clause is used to specify conditions on group
[04:25:04] functions. Group functions. If you want to specify the condition on group function, then having clause has to be used. But this clause whatever the clause is there, this clause always works along with the
[04:25:20] group by only. If a group by is not there, no having If a group by is not there, no having clause used. But having group by clause works without having clause. But having clause cannot be used group by clause.
[04:25:34] Having clause cannot be used without group by clause. Okay. That is what now you see there here whenever you come down here. Yeah. Bring this one.
[04:25:58] So what is that one here? Let me execute this query once. So like this we are having a so now I'm extending this question like this those max sals
[04:26:15] must be greater than or equal to 10,000 see there must be greater than or equal to 10,000 those max salaries
[04:26:31] so that means Here this 9,500 should not come. This should be avoided actually not healthy this one based on this is then here you have to write on having just see that again max of salary
[04:26:49] see that again max of salary greater than or equal to 10,000 you should write like this see I'm specifying condition on group function group function this is the condition is happening on function not on the column
[04:27:05] If it is a column we use wear clause. If it is column we use where clause but here it is the function. So that having clause but having clause can't be used
[04:27:17] clause but having clause can't be used without group by okay that is now you see there here see healthcare has been uh removed that means filtered that
[04:27:35] the same thing just see there here uh what is that uh uh number of employees in each department whose
[04:27:52] counter is greater than or equal to five. Greater than or equals to 5 that five. Greater than or equals to 5 that is then how can we write that having is then how can we write that having count of star greater than or equal to 5
[04:28:08] count of star greater than or equal to 5 that you can take down. senior data scientist and managers usually without that uh
[04:28:23] see there up to here in the sense what is the output you get over there see there 2 4 and 1 is there now those all has been reduced here
[04:28:39] is also going for four Okay. So like this you are having that is about the having clause
[04:28:52] that is about what here having clause is there here there here guys are you able to follow or not
[04:29:06] following or not these queries what I'm writing group by clause having clause writing group by clause having clause group functions
[04:29:44] Yeah. So this is one setma. This is completely one set here. What is that? Up to where group functions group by clause having clause up to from here. That is one set. First group functions
[04:29:59] are aggregate functions. Then on top of that group by clause then Then on top of that group by clause then on top of that having clause. Okay. This is and if you take some other examples here you can also include the
[04:30:14] where clauses also you can also include the where clauses you can also include the where clauses also here that is okay so that for that also we can do here that yeah now let's see that one query I'm asking here
[04:30:38] let me Show maximum salaries maximum max comma min salaries. What that is mean salaries
[04:30:52] retail under finance department. I ask like this retail and finance department. So here you see this how I'm writing
[04:31:08] that. So I'm including here group by and as well as where clause select what that is DBT max of salary comma min of salary.
[04:31:24] comma min of salary. Okay from emp group by d this is done.
[04:31:37] only retail and finance. I don't want remaining all then how you filter that is apply the wear clause. If you want to apply the wear clause guys so where clause should come always after
[04:31:52] from this is the order only you have to follow. You see this is the select statement order goes like this only. This is the only order we are having
[04:32:07] where group by having that is like that you will be getting that. So here see you will be getting that. So here see even see there if I write like this where so it shows us see where it is showing that DBT in
[04:32:22] retail comma then what that is finance showing an error that see it is not allowing you write the where clause after the group by so take this
[04:32:38] so take this after this you I know.
[04:32:58] guys? Are you able to follow or not? Understood are this quiry.
[04:33:27] so I'm taking to you last clause of the select statement ma that is here select statement ma that is here order by clause. simple clause. See there order by clause. This clause
[04:33:43] is used to show rows either in ascending or descending rows either in ascending or descending order of specific column. This clause is last clause of select statement.
[04:34:01] de S is something we need to get that is that means ma let's see that here
[04:34:15] order by clause so whenever we go for like this
[04:34:27] what is that here show all employees So in ascending order of salary ascending order of salary that is then we can go for like this see that select star from EMP for example see
[04:34:45] right now guys salary column see the salaries are here randomly there 7,65 or 3,000 something blah blah is there but I want First blah blah is there but I want First lowest salary then highest salary that
[04:34:59] then can we can write down like this order by salary. Now you see there how the data will come. come. See first 2,800 and gradually increases
[04:35:14] that. Okay. And finally you are having a Okay. And finally you are having a 16,500
[04:35:27] you are having. So after from EMP no other clauses has been used directly I used order by suppose if I get suppose if I get other
[04:35:40] clauses within it then order by clause has to yeah by default it is an ascending. So that if other classes are including order by clause has to move to the last it has to move on to the last
[04:35:56] that is one thing okay for example you see this take this query take this query and I'm using that so as per alphabetic order first finance should come first is finance should come
[04:36:13] but it is not coming no worries take this query here. this query here. Okay. Write now
[04:36:27] Then what should you write? Ma here you have to write order by DBD. Now see this result ma retail finance. Now finance and retail should come
[04:36:40] first finance and then retail will come there. there. Like we did so much of queries here. Like we did so much of queries here. Take this query.
[04:36:53] Take this query. Take this query here and apply order by clause. Order by first name Dc descending order.
[04:37:10] If you want a descending order, descending order will come here. That is on first name I did that. See first it starts with Williams and See first it starts with Williams and last it goes with I. Yeah. Jed to a
[04:37:25] last it goes with I. Yeah. Jed to a process will come over there. Jed to a process. Okay. So that is one thing just uh we are having. So order by clause is the here the last
[04:37:39] So order by clause is the here the last clause of our uh query. clause of our uh query. last clause of the query. It is
[04:37:53] like this. So
[04:38:05] show all employees in ascending order of the country. then ascending order of the country then ascending order of the country then first name
[04:38:22] So you just see that select start from emperor emperor by country first. by country first. See there now order by country.
[04:38:36] So country first what? See there Canada. So China see there Canada, China, So China see there Canada, China, Colombia, France, Germany, India, USA something is there. Now like see there Canada how many employees are there?
[04:38:51] Four employees are there. Their first name should be in order again only within this first what I want here now. Chad should come then Cynthia then
[04:39:03] Diana then Emily it should come like that in that case of comma you have to write first name first sorting will done on country after that then first name now see this result
[04:39:20] Diana Ched Emilage Cynthia is there now see the result see first ched see first Canada of Canada four employees are there that came as it is. Then you see here Ched,
[04:39:33] Cynthia, Diana, Emily. Same thing for China is okay. Only one employee. Then you go for here. Now what is another one country? Yeah, Colombia is there right now. Three employees are there. This is the Colombia part. Yeah,
[04:39:49] there. This is the Colombia part. Yeah, Smith. the first letter is same it take the second letter
[04:40:03] the first letter is same it takes the second letter that remember like that here now in that way it goes here now got it guys understood that is so multiple columns also you can take
[04:40:19] multiple columns also you can take ordering like this
[04:40:37] Yes. Yes. Mr. Naven, is it clear? Naven Ra. This is about today's topic here. Okay. We covered couple of commands. Still we
[04:40:52] have some more commands are there. some more commands and topics are there. So now here the leftover commands in the sense first of all here
[04:41:05] sense first of all here uh next week when we go to the next week here first of all commit then a roll back these two is two then a roll back these two is two commands has to go that is then one
[04:41:19] statement is there that is called case statement statement case statement that is one
[04:41:33] After this we have now there again now creating table using relationship primary key foreign key process. The fourth one is here joins joins process is one we are having. Okay. Then the fifth one is right now
[04:41:50] there uh views. Okay. These are the next week target. Even if the time permits we go for stored procedures, uh indexes and all that is here. These are the next week
[04:42:04] that is here. These are the next week one here because the these two are big topics. These two are big topic that is but these are the main targeted concepts in the next week. But if the time permits
[04:42:20] we go for other concepts also. So today's agenda is what we can call that subqueries is one table creation using foreign key. We haven't done that till now. We know how to create a table independently. That's but relationship
[04:42:34] between tables should go with the foreign key that is one thing. Then we can go for one more is there like uh joins here. Now we have joins process is there. Let's see that join process how it goes. Okay.
[04:42:49] So first of all let's see that uh what is a subquery what is a subquery what is a subquery what is a subquery and uh how the subqueries will work here and uh how the subqueries will work here how the subqueries will work out that so
[04:43:04] that is one thing just we do right now guys. So whenever we come down this one here the subquery means a query within a query is called subquery. We can call query is called subquery. We can call a query within a subquery.
[04:43:19] One query within another query we can call it as a subquery. As for that as call it as a subquery. As for that as for that process guys here one select command we are going to write to another select command.
[04:43:34] one select command we are writing on into some other select command that okay so that is one thing we can do as of now okay now let's see that if [snorts] so
[04:43:48] what is the purpose of this subqueries so why should we write this subqueries in the sense of generalize a query generalize the query where we no need to specify fixed values what that is specify I
[04:44:06] specify fixed values. Fixed values. So in wear clause What are the in where clause the conditions that this is what has to go
[04:44:21] some not all the time. Sometimes we need to specify the fixed values that is compulsory. We can't avoid completely. But sometimes not every time but sometimes we can avoid and we have to be
[04:44:34] avoid we have to be avoid specifying fixed values while writing conditions on wear clause. So what are that? What are that one here? I'll show you one thing. Let's see.
[04:44:49] So last class we have been discussed about uh like aggregate functions or group functions. Aggregate functions are group functions. Aggregate functions are group functions. See there now here select max of sal
[04:45:15] you are finding the only maximum salary here. Suppose sir if you ask like sir can I get what who are the what we can call that employee who is getting that
[04:45:27] maximum salary that employee details I want right now that employee details I want right now there so then how that can be taken up usually what is the maximum salary we got right now 16,500
[04:45:42] okay that here now so select the star from EM where So what is that here now? from EM where So what is that here now? Salary is equal to 16,500. Usually we write like this just listen my point.
[04:45:58] Usually we write like this. See so is Arthur block CEO is the role of that person. So [clears throat] getting now 16,500 is the salary maximum salary. Okay. Fine. Okay. Like today he is getting 16,500 is the salary. After few
[04:46:14] days he might get salary hike it may go to 1 lakh 65,000 something else some to 1 lakh 65,000 something else some hike has been done then is this query is useful on that time in the sense no that query is not useful again what you need
[04:46:29] to do here so first we have to find the maximum salary and we have to know about the salary and that has to be substitute here value that value has to be substitute here it should be done like that means so the to solve this kind of
[04:46:44] problems we need to write two steps two select statements so can't I get this entire thing in one statement whatever it is the maximum salary doesn't matter
[04:46:57] whatever it is the maximum salary doesn't matter it has to show all the employees so who is getting maximum salary all the employees who is getting maximum salary I want like that then how okay
[04:47:10] that is what here let's see in the place of where you are mentioning fixed value. Listen carefully guys. In the place of where you are giving fixed value 16,500 fixed value wherever you are mentioning in that place of by writing another
[04:47:26] query by writing another query to get the maximum salary that means can I write like this here? Now see there select max of salary
[04:47:40] from EM we will get like this. See there now in a simple words say same answer I got it right.
[04:47:52] answer I got it right. So now I we no need to write here this query. See there we are writing one query See there we are writing one query within another query. It is now we are
[04:48:04] able to get this one. So let's see that here. Now uh whatma So let's see that here. Now uh whatma that is show
[04:48:22] who is getting max sal something like that. In the same way see there now show an employee details who is getting min
[04:48:34] lowest sal. So can we go for like this here? Now what is that mean minimum is the function is there that aggregate function I'm utilizing right now that
[04:48:46] function I'm utilizing right now that see there now 2,800 is the minimum sal but how just I'm showing the queries but to write this subqueries one here to write the subqueries one we have some rules
[04:49:02] yes that is a simple see that what are the rules here see a subquery must the rules here see a subquery must return see must return single value or return see must return single value or single column values. Single value or
[04:49:17] single column values. Single value or single column values based on operator used. That is the first one which means That is the first one which means actually from this one just from this
[04:49:32] where subquery in the sensema the subquery goes like this here. subquery goes like this here. What is that? So this is what a subquery has to be written in where class of main query better you make uh this one into
[04:49:49] query better you make uh this one into the top then that is second point in var class of main query and second one what is that subquery must return
[04:50:05] single value or single column values based on operator used. Then here first based on operator used. Then here first see that first subquery will be executed see that first subquery will be executed based on the subquery result main query
[04:50:19] get executes main query get executes here. So that is what just we have to go for now. So which means when it coming to right now there you see this is the subquery. This is the subquery here. This is
[04:50:36] called subquery and this is called main query. This is called what here? Main query. This is called what here? Main query. Okay. First subquery will get executed. Whatever the subquery you write that query will get executed. You
[04:50:51] will get an answer. So suppose see I selected this part now then go for selected this part now then go for executing of this see 16,500 is there okay that is one thing then that now select start from emp where salary equal
[04:51:06] to 16,500 main query will got executed that and here also same thing guys first subquery will get executed 2800 will subquery will get executed 2800 will come here now see that 2,800 then main
[04:51:19] query goes select start from emp salary equal to 2,000 800 the minimum salary employee details will come that is okay and that one thing this subquery must be written single value one value
[04:51:35] must be written single value one value or else one column values that two based on the operator the operator what we used here this is what the operator
[04:51:47] here now you are using here equal to or else less than equal to or greater than that guys if you are using greater than, less than, equals, not equal in the
[04:51:59] sense. So how many values it will take? How many values it will take guys for a condition? How many values it will take? take? Only one value. Yes, that is
[04:52:14] Only one value. Yes, that is okay. one value that one. Suppose in okay. one value that one. Suppose in case if I write in operator then how case if I write in operator then how many values it will take?
[04:52:28] it will take? That is just a multiple values. That is what here. Now in operator in the sense multiple values that is okay multiple
[04:52:41] values commas operated that to on multiple values of multiple columns are single column. Multiple values of multiple columns are single column. That you tell me guys guess it
[04:52:58] single column only that point you have to remember okay see where salary in for to remember okay see where salary in for example like see salary in suppose if
[04:53:10] I'm writing like this 10,000 okay like 20,000 30,000 if I write like this these all salaries from these all values from the salary column only so
[04:53:22] mult multiple values of single column that is the point here we I'm mentioning here now see there you can that is a subquery
[04:53:34] must be written single value or single column values based on the operator used the operator what you are using here okay equal to not equal less than or
[04:53:47] equal to less than greater than or equal to greater than if it is like that single value in operator in the sense multiple values of single column based on that only here you have to write the query
[04:54:01] it's not always max or min some other queries also you can write but whatever the query you write that return single value or single column values that's it that point you keep in mind okay that is my here now see there with the subquery
[04:54:17] s we got it here this way so this is why let's have another example of subquery can I get like see see
[04:54:29] this is what here now showing maximum salary it is what that showing maximum salary it is what that showing maximum salary it is suppose if I ask okay show
[04:54:42] salary it is suppose if I ask okay show second maximum salary I want only second maximum salary not second maximum salary employee details I want only second maximum salary I want it here
[04:54:57] then how can we go for that second maximum salary here yeah with the help of subquery I'm solving this see there select max salary
[04:55:09] solving this see there select max salary from emp it's a very little tricky here where salary less than within this again going If I select max of salary
[04:55:24] select max of salary from EMP that I'm right from EMP that I'm right understood this one properly
[04:55:37] understood this properly there see there 14,500 we got it maximum salary is 16,500 second maximum salary is 14,500 is there how we are getting that? So analyze this query. What is the operator we used
[04:55:53] query. What is the operator we used here? What less than first? This is what you get there. Listen carefully. Select max salary from EMP in the sense. So you get 16,500 only that is subquery is written 16,500.
[04:56:10] Now you understood select max salary from EM where salary less than 16,500.
[04:56:22] salary from all salaries of salary column by excluding by excluding 16,500 because you used here less than operator.
[04:56:36] What you used here less than operator you use excluding 16,500 that so obviously that will come or not second maximum salary will it come or
[04:56:48] not ma clear or not that query please let me know are you able to follow or not
[04:57:02] again if you put now less than or equals to again 16,500 will come It it goes with a purely operator. You see that if I go for now less than or equals to then there is no meaning for that one 16,500 only you'll get. So
[04:57:18] that you have to go for only less than that maximum salary exclude. Now we are that maximum salary exclude. Now we are getting this is
[04:57:33] yeah like this we get and one more that you see this here what do you say that is yeah yama so show yeah yama so show an employee details
[04:57:53] second maximum cell now you see there now see there now so for this so I'm going to write a subquery within a subquery you can write like that
[04:58:07] a subquery within a subquery you can write down you can go with subquery within a subquery you can write like this it is possible one subquery within this it is possible one subquery within another subquery you can write down sir
[04:58:22] subquery for this see ward I'll show you now just wait first let me finish this that's what I'm telling right now ward the same the
[04:58:34] situation will come here show second sorry employee details who is getting second maxel okay so then can we write like this select star from emp
[04:58:48] where you get it down here Now salary is equals to okay can we get this this is giving okay can we get this this is giving second maximum salary that query data
[04:59:03] second maximum salary that query data I'm adding here I'm adding here that I'm adding see there one subquery within another subquery an employee details who is getting
[04:59:15] an employee details who is getting second maximum salary 14,500 that subquery I'm writing one more subquery here. So how this will get
[04:59:28] executed? How this will get executed? Ma first How this will get executed? Ma first this will get executed. Okay. 16,500 this will get executed. Okay. 16,500 will come. Then continuation to this
[04:59:42] will get executed. 14,500 will come. Then this will get executed. That employee details will come. reverse mechanism in a reverse order you have to go for that in a reverse order you have to go for
[04:59:56] that here guys understood or not this query
[05:00:08] understood or not ma guys please let me Oh,
[05:00:33] can't go for more than maximum salary. No. anything whatever you have to do that ex up to maximum salary only the same thing we are doing here yes your calculation is correct whatever you are thinking that's correct here
[05:00:51] we cannot go for greater than maximum salary right we cannot go for greater than maximum salary that is if it is from all the employees if it is if you go for any group by clause or something then you
[05:01:05] can That is possible. Okay. But we are not using any group by clause here. So that's what that's what [snorts]
[05:01:18] third maximum salary in the sense. So on the basis of this one you can solve that. What is that here? Now see there here third maximum salary in the sense there same thing again you
[05:01:33] have to go for like this. For this again you write one more where clause that's it where salary again going for less than
[05:01:45] where salary again going for less than what that max of salary from EMP what that max of salary from EMP we are taking like this obviously you get third maximum sal,000 is there
[05:02:01] is there got it ward
[05:02:14] So that is one thing. Suppose a little bit by like see uh yeah. bit by like see uh yeah. So here if I write like this show an employee details who is getting
[05:02:29] max and mean salaries. and mean salaries. How we write a query for this? How we write a query for this mark? I want an employee details who is in one
[05:02:43] query. I want maximum salary employee and minimum salary employees. Both two and minimum salary employees. Both two employees I want right now. Two employees we can write on over there. Shall we go for like this?
[05:02:58] Select star from. Can anyone try that? Please star from. Can anyone try that? Please try. I want an employee detail. So he's getting max and min
[05:04:20] I'm giving one clue also. You have to use in operator.
[05:05:00] Ma'am, should I write
[05:05:29] answer we get. See current has given mark current has given answer that see there select start from emp where salary in
[05:05:43] just I'm taking like this here. Now you write a write a selector. Okay, one thing we do here
[05:05:57] okay from empery put a comma then you go for that here select max of salary max of salary yeah
[05:06:15] from emp okay then this is done. See there this one this is one method. So first what month
[05:06:28] two subqueries so in one way class two subqueries has been written independent This will give you maximum minimum salary. This will give you maximum salary that works on in the inoperator. See there now you get those two employee
[05:06:42] See there now you get those two employee details.
[05:06:54] 16,500. The two employee details we got it there.
[05:07:16] Fine. Why we are using brackets for getting values of quaries brackets part it's not I think so optional guys here it's
[05:07:28] no see actually here it won't allow you here's comma after completion of query semicolon will allow you so that is what see it is not allowing you to write a second one because of that if you keep
[05:07:42] that parenthesis is okay now it works there got it Navindra Naven Raj sorry
[05:08:06] concept we'll go for that guys here now we have some other queries also there we'll do it that we will do it that is here now. So or else you write one more query. I'll give like this here. Now uh so
[05:08:29] who are having who are who have above the max salary
[05:08:41] above the max salary above the max salaries above the max salaries of what we can call retail department I I want like this now retail department
[05:08:56] retail department retail department that tell me now now write the query for that show all employees who are who have maximum salary from retail department
[05:09:21] So first find the maximum salary from retail department and show all the employees who is getting above that maximum salary of retail department
[05:09:37] that is one thing just we have to go how you go that shall we write like this select so first of all you find the maximum salary so select tax of salary
[05:09:51] so select tax of salary from EMP see there now here where what from EMP see there now here where what that is so DBT is equal to what we can that is so DBT is equal to what we can call retail shall we get like this
[05:10:06] that will give you the see that 10,000 we got it that so then simply you go for that here select star from EM where Okay. Salary
[05:10:21] select star from EM where Okay. Salary in greater than this is above no I asked about that so that we go for here now
[05:10:35] about that so that we go for here now see there here there now which is from different departments
[05:11:00] that first you write a subquery based on that first you write a subquery based on that you go for there.
[05:11:12] another way. Of course the group by clause we no need to add. Even if you add the group by clause that also fine. Sometimes group by clause also will come here. What is that?
[05:11:27] Uh see there this clauser you can make it like this. So this one [clears throat] usually we write like this. No
[05:11:42] group by what meth that is dd I'm writing. So all department maximum salaries will come from that uh you get it only uh like what retail department group by uh maximum salary.
[05:11:58] maximum salary. So that you have to add it here. So this this is also will give you retail department maximum salary only. Yeah. See there that is also fine.
[05:12:11] that is also fine. This also you can add it here.
[05:12:30] on that without group by clause with the group by clause we have been solved that there are different ways to write the queries that has been given.
[05:12:47] querying of this what we can call subqueries. We'll do some other subqueries with assignments. We'll do that. Not a problem. Let's move on to another topic called in our uh today's agenda table creation with the foreign
[05:13:04] key. Table creation with the foreign key. Guys before going that please let me know anybody having any queries or else so is everyone understood writing the subqueries guys
[05:13:25] understood if anyone having any query please post if anyone having any query please post it here so that we'll discuss
[05:13:43] Please guys, so I'm moving on to next topic. Right.
[05:14:08] So this is about uh let me save this one like a sub queries 21st February like a sub queries 21st February 21st subqueries. Yes, this is the one I'm just taking
[05:14:24] that. Yes. So going to another one. Yeah. >> So now the next point here now let's see that next topic for today's one that so
[05:14:38] creating relationship between tables using primary key and foreign key that is what we are to go how primary key and foreign key part will work here that is that means guys here the relationship
[05:14:54] has to make between the tables first of all why this relationship should So, so for that I'll show you one thing that for that I'll show you one thing that here
[05:15:17] here now uh so what do you say that is for example you take a student by data for example you take a student by data table is one we are having here so role table is one we are having here so role number is there student name is
[05:15:30] number is there student name is time. Okay. Role number, student name like we go for gender. So now here city something we have something like this here.
[05:15:45] So now we are having that this is some name something that is here 1,002 some name something that is here 1,002 something like see
[05:16:03] we have one that is three students are there here so now I'm taking that uh marks is one table you are having in this marks table. So what I'm taking you
[05:16:16] this marks table. So what I'm taking you know serial number exam subject one know serial number exam subject one marks subject two marks total and here marks subject two marks total and here I'm giving role number
[05:16:33] something like uh so what you say that here so quarterly is an exam here so quarterly is an exam okay some 56 78 okay some 56 78 some we got some total here now let's go
[05:16:47] for that so 100 then whose marks it is that need so 100 then whose marks it is that need to go suppose let's say that 1,002 okay then here this is so so what we go for 60 again 60 120 it is there 1,1
[05:17:06] let's see that is so 3 again quarterly 45 and it is 90,0003 something just you go for like this m so here suppose if I'm going for another
[05:17:19] student like this so what is that one here now let's say that whose marks it is suppose let us say that if I add 1,4
[05:17:32] that if I add 1,4 it should not accept here because here one thing that guys here the marks what we are entering here the the marks what we are entering here the marks Okay, that marks are completely it
[05:17:46] depends on the the students who are that who are there in the by data table. who are there in the by data table. 10,001 role number 10,002 10,003 is there now that we are taking this is okay this is also okay this is also okay
[05:18:01] because these role numbers are there here but uh it is not acceptable if I want the role number who are having role number 104 by data details can we
[05:18:14] get from this table can we get it from this table we don't have that we cannot get it that one. So data inconsistency will happen data inconsistency will happen. It's a
[05:18:31] excel so that we are able to write but it should not happen on a databases so that uh we have to make a relationship between these two columns.
[05:18:46] We have to make relationship between these two columns here that is okay. So whenever we make relationship between these two columns now here it is between these two columns now here it is a primary key here it is what primary
[05:18:59] key it is simple wayma that is it is a primary key and here it is a what in the sensema here foreign key
[05:19:18] primary key and foreign key will go value. So in foreign key column whatever the values you are entering the this column will refer always the value existency in primary key of by data table in this role number column
[05:19:34] entering here 102 first it has to check that 102 is there or not here if it is there it will accept it has to accept okay if it is not there it won't accept here
[05:19:49] so data consist prospection should go prospection should go see employees suppose a department table employee table. So employees are registered in a department based on the
[05:20:03] registered in a department based on the department only. That is one thing okay like uh like you can so you can go with like country names and their capital cities. See there now first I'll write all the
[05:20:16] countries then I'm going to add the capital cities. capital cities. So based on the country reference so that in that way just it goes there the relations here.
[05:20:30] So there are a different ways that here now see I'll show you now there now see I'll show you now there you delete this one here just see that so the relationships you can take in four ways what that is the relationship
[05:20:45] you can take it in now four ways that here first relationship is one to one is there now what that is one to one relationship will be there.
[05:21:04] So one one to one in the sense there. So one entity here entity A is there then entity B is there. Okay in between that we will make a relationship.
[05:21:16] This is called here now one toone relationship. What is that one to one relationship here? here? See there now one to one relationship.
[05:21:34] So an example if you go for that is an example if you go for that. So let's see that what you can say that is yeah student student course. Can we make it like this?
[05:21:49] One student one course. one student one course one to one relationship can take that is here. So that is one we can take down there. So that is one we can take down there. So one more is there here that one to many.
[05:22:04] one more is there here that one to many. What that is one to many is there one to many and many to one also you can go for reverse order that go for reverse order that what is this is one just we can take
[05:22:19] down here this is one entity having one entity having here a relation one entity having relation with the two other entities here two other entities is.
[05:22:35] two other entities is. So here you can go for this one. This is the one just we can take this is called here one to many. One to many that what is that my here now sales report. Just see there sales
[05:22:52] now sales report. Just see there sales report consist report consist what that is consist of customer and product details product details here
[05:23:06] product details here here this you can make sales and this is going for whatma here let's consider so that customer and it this you can take whatma that is product something
[05:23:19] you can and you can make reverse Also many to one also going for same thing many to one also going for same thing that what mother is many to one also you can make that is that that is also one more point you
[05:23:33] that that is also one more point you will get there So there are three has been covered. Three has been covered here. That is
[05:23:46] next. Fourth one. Here that is many to many. Many to many that we can take down that Many to many that we can take down that is.
[05:24:01] So this is one more just you can take down here. So how you can go for that is right now there so A and you make it see that and
[05:24:16] there so A and you make it see that and here uh this is B and you can make this is a D. Okay that is one thing just uh we are Okay that is one thing just uh we are getting that. So A to C you can get A to
[05:24:30] getting that. So A to C you can get A to D you can get B to D you can get and B to D also you can get this is called here now many to many what that is many
[05:24:42] here now many to many what that is many to many you can take that is here now many many to many relationship that what is an example for this many to many guys can anyone guess for that can anyone guess for that Yes.
[05:25:11] is one that see multiple employees are participating into multiple projects. Can we take like that projects are multiple employees are so multiple? One employee one project not one employee might be participating into
[05:25:26] one employee might be participating into multiple employees or multiple projects. Okay, that is multiple employees multiple projects you can take that is and a simple scenario method. What method is multiple employees
[05:25:41] multiple products products okay that you can take down here.
[05:25:54] down that right now. Yeah, this is the one kind of many to many that is. So this how can you make that? How can you make the relation here now in a data in the form of database tables there goes a primary key and foreign key
[05:26:11] concept. Let's go for working on that primary key foreign key concept by primary key foreign key concept by creating some tables here. So here so what is that you know see how the parent sorry for primary key foreign key
[05:26:26] we'll go for here now when it coming to this one guys see parent and child tables must having a common column that is the first rule yes in excel if you
[05:26:38] see that see here role number column is there here also role number column is there here also role number column is there that is one thing second point in parent table common column should have a primary key.
[05:26:52] consider this is the primary parent table. Okay. So then this should be a primary key in a child table. Common column will have foreign key. Obviously that common
[05:27:07] column in child table becomes here now foreign key. See here in primary key here you have a unique values but here you might have the duplicates. How it is in this table only? Just check that. So three students
[05:27:21] has been written quarterly exams. Suppose if half early exams has been written that okay again the role number will be repeated. No role number will be repeated because of
[05:27:35] role number will be repeated because of that it's our half exams that it is what here now half exams that we go for there.
[05:27:52] Okay. So like this. So we can go for it here. This is in this way. So that is called we can get it right now. So let's see that how to apply this primary key foreign key one there. So parent table creation we don't have any new concept
[05:28:07] that that as usual we know how to create a table. Here we have to see that how to apply the foreign key. This is the syntax we have to follow the foreign key prospection. See foreign key column name
[05:28:21] foreign key how do I write on that column name references parent table of column name references parent table of name column name has to go yeah let's see now that guys this is the
[05:28:33] example we'll make it that here now one point we'll make it like this what is the first I'm taking parent table as a customer serial number unique that means here it is a that okay leave it there customer name location, account number,
[05:28:49] primary key. Then here see transaction, serial number is a primary key, account number, transaction type, transaction date, transaction type, transaction date, account number is a foreign key.
[05:29:01] Let's see that how can we make these two table creations here. table creations here. So let's go for that here. Sorry, where So let's go for that here. Sorry, where is that? Yeah, here you are having that.
[05:29:26] So then here first I'm creating a parent table. So that create a table customer. So that you write that is serial number. It is into unique.
[05:29:38] Okay. Int unique I'm taking that. Then C name I'm making that is workar of some 20. Then location I'm making that so where
[05:29:50] care of 10 so make it a default value okay what so make it a default value okay what that is hyd
[05:30:07] what is account number worker of some 10 so primary key see unique key in the sense no only duplicates won't allow but a null value
[05:30:23] duplicates won't allow but a null value is possible one null value is possible primary key in the sense no duplicates no nulls that is the difference very good question that
[05:30:37] everyone thinks that primary key in this sense only unique not that one primary key is a combination of unique and not null unique in the sense now there unique in the sense duplicates won't be allowed
[05:30:50] but one null value can allow one null value can allow you see that one null value can allow you see that guys I'll show you here for example okay here only you just take that it is not a don't consider like a relationship
[05:31:07] here only this one I got here now unique constraint what the unique constraint client. Okay, here I don't know one of the student role number. Fine, no worries. I'm writing name and I'm giving that and
[05:31:22] again I'm going for that. Yeah, that's what that is I'm explaining. Namin RA just see there. Let me finish this. Okay,
[05:31:39] student uh role number I don't know. For example, if I'm giving like this here example, if I'm giving like this here again here it won't allow it won't allow already one null value you used one more null value in the sense again unique
[05:31:54] constraint violated again unique constraint violated here so that is one point you have to remember it won't allow here no
[05:32:06] there is a chance of only one null value another null value in the sense again duplication is H again duplication is coming here so it won't allow if it is a primary key even that null value also
[05:32:19] will not be allowed completely unique and not null I hope you got it that and not null I hope you got it that understood huh
[05:32:36] yeah that makes the difference here now yeah now I am creating parent table here yes I created a parent table now let's go for that child table that so here
[05:32:49] create a table transactions I'm giving trans only making some shorten names so trans only making some shorten names so that serial number in uh I'm giving here now auto increment it is a primary key for this table auto increment in the
[05:33:05] sense you can you no need to enter the values into that table we I already discussed starts from 1 2 3 4 it will go then I'm starts from 1 2 3 4 it will go then I'm giving okay then t type you just write
[05:33:18] giving okay then t type you just write on t type is workare of some 10 then t date transaction date it's a date column you just make that easier now then
[05:33:31] you just make that easier now then amount what is the amount has been sir one second that h here account number you write on it's a workare of 10 I'm write I have written that then
[05:33:46] 10 I'm write I have written that then here what is another one amount just go for that it is int yes so like this we mentioned now for this column we wanted mentioned now for this column we wanted to make primary sorry foreign key
[05:34:01] for this column we wanted to make what that is foreign key here now here you write down Yes. Yes. Foreign key of account number.
[05:34:14] Okay. References. Customer. Customer of and customer. What is the column? It has to go for refer account number only. Yes. Like this. You have to
[05:34:28] write applying the foreign key like this. applying the foreign key like this. Then here you go for this and execute. Then here you go for this and execute. Yes, two tables has been created here.
[05:34:41] Primary key, foreign key part has been taken right now. So now two tables are in relationship that is. So here you see that is right now. So already we discussed about a describe command. Describe customer if
[05:34:58] you go on that that is okay. It will shows you like this. Yeah. So that is one thing we are having then. Now, so describe France is the table I
[05:35:10] just created right now. There [clears throat] see here you can see that is so if it is having mul that means it is having foreign key constraint that is the meaning you have to remember.
[05:35:26] M U in the sense of here whatma that is foreign key. You can by seeing the structure you can say that from which column to which column you have a primary key and primary key and foreign key that is
[05:35:38] there guys.
[05:36:02] getting right now. Okay guys, that is one thing. Now let's go for data adding. So we wanted to go for insertion of data. Insertion of data always should be done. First parent table, then child table.
[05:36:18] Insertion of data has to be done. First parent table, then what? Child table. Okay. Add some rows into the parent table. Add some rows into parent table guys that here
[05:36:32] that here insert into what that is customer. So insert into what that is customer. So you just go for serial number C nameo then account number location is a default one so that I'm
[05:36:45] location is a default one so that I'm skipping that here values serial number one customer C name that is so here something like I'm giving some name something like I'm giving some name ACC 00001 I'm writing that
[05:37:05] let's have three accounts Yes.
[05:37:21] Now just three rows are inserted. See there just three rows are inserted. See there right now there
[05:37:36] foreign key. How we have to check the foreign key here. Let's go for that insert into serial uh okay transfer. Okay. So open
[05:37:49] serial uh okay transfer. Okay. So open bracket that is see account number then bracket that is see account number then t type then t date then whatma that is amount. Yes. Then values see there account
[05:38:03] number to which account it is. So that here you just see there AC acc. Let's go for that one here. Then transaction type debit. Then transaction date let's say that here now first year 2026
[05:38:21] here now first year 2026 that is 02 - 10th date. Let's go for that is 02 - 10th date. Let's go for that. It is a 10,000 amount. Okay. I got it like this. So this row will be inserted. Now
[05:38:34] see the row has been inserted because account number two is there. account number two is there. So let's check that. Uh sorry, let's check that the account number which is not present on the parent table
[05:38:49] while inserting the rows. Okay. Now here you just check the now I'm checking the foreign key. So here I'm just going for account number four. Keep it like all the transactions are same.
[05:39:03] Just go for this. Let's see this is see you got an error here. See there you got an error over there. Here now it is not inserted. What is
[05:39:15] Here now it is not inserted. What is that error statement? See there here. So error statement is what must see there. No error code 1452 cannot add or
[05:39:29] update a child row. A foreign key constraint fails. Foreign key what? That account number references to the this is. So you are trying to add account number four but account number four is not present on
[05:39:43] the buy data table. So it is not accepted. That is what here we are avoiding inconsistence data. We are avoiding inconsistency data. For example, if I go for that 100,9 it should not accept because 109 is not
[05:39:59] presented here. Okay. So this is the one just we have to Okay. So this is the one just we have to go. Now you go for this is here one. go. Now you go for this is here one. Okay. Let's see that is right now there
[05:40:13] is accepted. See that is now you go for account number 10,2 account number 10,2 let's go for that citor and here now some 14th date okay the something yes accepted
[05:40:29] and again you just go for that what do you say that is this is something like a debit what that is debit some thousand rupees
[05:40:42] ctions some random transactions I'm adding that adding that now you go for here see there now got it up there the something like we
[05:40:56] got it up there the something like we are having this is understood or not guys please let me know if the primary
[05:41:09] creation is understood between the tables tables What is your query here? Uh if I want to What is your query here? Uh if I want to create
[05:41:24] Yeah. Now come down to so here account number is primary key. So it won't allow duplicates and nulls. And when it coming to here you see first what I did here I have given all the list of columns in transaction table.
[05:41:40] I have given all the list of columns in transaction table that finally what I started here now see and now to this column I'm wanted to give foreign key
[05:41:52] there something some relationship which refers to the customer table of account number column so what I have written foreign key of account number what is this account number this account number is the column name of this is this
[05:42:05] column name this name it this column name I'm referring So it should be depends on that means references it should be refer customer [clears throat] table it's a parent table now customer table on the customer
[05:42:19] table to which column it should refer account number column account number column like that it goes
[05:42:36] any other queries here guys please let me know I hope that guys please let me know I hope that everyone is able to follow right right now in this relationships so two points has been done first of all
[05:42:53] so two points has been done first of all uh is only one multiple columns should not get the multiple columns should not get the primary key remember that one
[05:43:07] Unique you can give any to any number of columns. Primary key is only one constraint for that one to the multiple columns. We cannot give primary key.
[05:43:25] So this is the one we go for a deletion. Suppose right now for updation is okay. Leave it that. So for a deletion what we have to do it in the sense see that guys first we have to delete rows from child table then parent
[05:43:42] that means ma for example just come down here guys try to follow
[05:43:55] so select star from customer is there okay I'm just going for this is yeah now okay I'm just going for this is yeah now I wanted to details I wanted to delete pawani details here pani details I wanted to delete
[05:44:10] let's see that whether the delete command works or not here delete from customer the simma here where account customer the simma here where account number is equals to here acc00002
[05:44:26] I am writing let us see that here now whether it deletes or not. Let us see whether it is deletes or not here. Now see it is not deleting that row.
[05:44:39] Why? Because in the sense now with the customer with account number 002 we have some transaction details in transaction table dependency is there. If there is a dependency how it should be deleted here it's not possible. The
[05:44:56] same thing it is saying here. Now see that that the same thing it is showing here that so whenever we go for this one see delete from customer where account
[05:45:09] delete from customer where account number equal to 0 to see error code 14 what that is so 1451 cannot delete or update a parent row.
[05:45:27] a parent see this one here now a foreign key constraint fails key constraint fails a foreign key constraint fails that here what is that one see the dependency is there no references of this is
[05:45:49] I want to delete then how okay so it is not deleting because you see here that In a transaction table guys with the account number two we have a multiple transactions is there is a transaction one is there and transaction four is
[05:46:04] there if I delete there what is the relationship here now no way that's why it won't delete suppose okay you have decided sir I want to delete I don't want I want to delete then what to do
[05:46:19] first you delete all the rows from the child table first you delete all rows from child table then go on to delete the parent table like that it should go now see there
[05:46:32] like that it should go now see there here now what I'm doing here the same delete command first you delete rows from the transactions okay trans you go
[05:46:49] yes see there from the child table we deleted rows has been deleted two rows has been deleted You check that here. See there [clears throat] rows one account one is there here.
[05:47:04] Now you delete the rows from the customer table. table delete command has been worked here. Now you see this
[05:47:23] transactions for account number three in the transaction table? Is there any transactions in account number three in transaction table? Ma
[05:47:39] no we don't have any transactions. So here if you want we can delete that uh account details from the customer table. Here we can delete that is because there is no dependency there. Okay. If you want
[05:47:53] you can delete that is by keeping off this one here. Account number three there like this. You want to see that? Yes, you can do that one. See sir how it is deleted without deleting
[05:48:09] transaction table we don't have any transactions for the account number three. So it will delete if any dependency is there it won't in that case first you have to go to the child table on the child table you have
[05:48:24] the parent table then delete that particular uh account details but it is particular uh account details but it is a little bit cumbersome task or not
[05:48:36] guys tell me little bit cumbersome task or not it Obviously something but when you go to the live databases you have n number of rows are
[05:48:52] there some hundreds of rows thousands of rows or something that is so in that case so going to the child table searching the data deleting coming back to the parent table then deleting searching the data deleting in the sense
[05:49:05] searching the data deleting in the sense cumbersome task. So database will give us one option. What is that? In the sensema, if you apply a delete command sensema, if you apply a delete command on parent table, if you apply a delete
[05:49:19] command on parent table, that command of that delete command has to delete rows from the parent table as well as its child table relevant to data also. If it
[05:49:32] child table relevant to data also. If it is like that, it will be easy for us. Got it ma? that flexibility. If you have that flexibility, then that would be that flexibility, then that would be fine. Anyhow, you have decided one row
[05:49:44] from the parent table. So there is no meaning of that by deleting a row from the parent table by having data into the child table. So delete row from the parent table and as well as delete that
[05:49:56] relevant rows into the child table also. Then how can we do that? In the sense of while creating parent sorry while creating child tables while applying creating child tables while applying foreign key there is a an option called
[05:50:10] foreign key there is a an option called there are two options is there on delete there are two options is there on delete cascade or on update cascade. On delete cascade or on update cascade is there.
[05:50:25] is there. What are that one? See ondee cascade. Ondee cascade. When we apply this option to a child table. When we apply this option on a child table, it allows to delete rows from
[05:50:42] parent and child tables at a time. When we apply delete command on parent table
[05:50:54] option to a child table, it allows to update relationship column values from update relationship column values from parent to child table at a time when we parent to child table at a time when we apply update command on parent table
[05:51:08] that is what? So shall we apply these two options right now by creating two options right now by creating another pair of parent and child tables? another pair of parent and child tables? Shall we split that m?
[05:51:23] tables that so just have some few columns less number of column that would be better. Now here I'm creating a table dpd department that is department number in
[05:51:40] primary key it's a parent table now primary key it's a parent table now department name okay where caro sum 20 okay that's enough two columns is enough because concept is important that's find
[05:51:55] that here are two columns so insert into db PD okay department number department name okay values 1 sorry 10 now so then it is
[05:52:12] a sales department so then here 20 so then here 20 here now education department here now education department so then one more here uh 30 so here like
[05:52:27] what do you say that is research department like the three departments I added now let's have that if yeah fine let's have that if yeah fine now I'm creating here create
[05:52:47] okay here now emp number it is into primary key then e name where care of some 20 I'm not adding all other columns leave it that Okay, department number
[05:53:00] column I'm taking as a int. Just listen my point. Department number column as an int that is has been taken any other columns we don't want that as of now.
[05:53:12] Now see that I'm applying foreign key okay foreign key of what we write department number references what I'm taking that dpt of department number after writing this here you write
[05:53:29] number after writing this here you write on on delete cascade space on update cascade you have to write like this then close it you have to Write like this here now.
[05:53:45] you have to Write like this here now. Okay. Then go for executing that. Now Okay. Then go for executing that. Now insert the employee data ma here. Insert insert the employee data ma here. Insert employee data. See there insert into emp
[05:53:57] info empa eame department number. eame department number. Okay. So then go for values 1.
[05:54:09] So Ramana. Okay. Here now. So department number 20. Okay. Here now. So department number 20. Yes. You add some of the employees.
[05:54:21] Yes. You add some of the employees. You add five employees. That You add five employees. That like we have some employees here. this one here? EMP info.
[05:54:38] I'm just taking that. Yeah, got it. So now you apply a delete command on a DD table. So to check whether the ondee casket is
[05:54:52] working or not, what I'm writing delete from DPD where department number is from DPD where department number is equals to I'm deleting department 20.
[05:55:04] Just see there now see guys listen carefully. I'm not applying delete carefully. I'm not applying delete command on emp info table. It's a child table. I'm [clears throat] applying delete command on dep table which is a
[05:55:16] parent table. Deleting department number 20. Department 20 I'm deleting. Yes, it got executed successfully. One row is affected. Okay. Now you check the data in both tables
[05:55:36] Department 1D is not there till now you check till now you check data in EMP table employee info table okay see there now department 20 employees are deleted automatically but
[05:55:51] I haven't applied any delete command on emp info emp info on delete cascade
[05:56:05] okay guys I'm moving on into some other topic called joins here. Let's see what topic called joins here. Let's see what is meant by joins and all that is guys. So before going to discuss about the joins first of all make our uh to write
[05:56:19] the join queries we have some joining tables are there. So that table. So I hope that last week you have been downloaded that SQL data sets right? You downloaded that SQL data sets right? You are having SQL data sets. Oh,
[05:56:39] from that data sets guys you see there here SQL data sets go to assisted practice data sets on that assisted proxics data sets lesson number five is
[05:56:51] there sorry not that
[05:57:03] le data sets is there in this leap data sets you have very lesson Five retail M management data set is there. There you have some three CSV files. I hope that
[05:57:16] everyone got it on your side. Right. Please check on your side guys and please confirm that you are having or not these three Excel sheets.
[05:57:40] You downloaded everything from the last week only. SQL data sets you downloaded last week only from your LMS platform. only from your LMS platform. There you have got uh
[05:57:54] There you have got uh so lec this is the SQL data sets folder. This is so this is what here and go to within that three folders existed one folder you go for le data sets from that one
[05:58:09] lesson five retail M management data set is there there you have got uh yes there you have got these three excel files if anyone is not getting I'll share it
[05:58:24] if anyone is not getting I'll share it on into the chat box if required it up. on into the chat box if required it up. Yeah, got it up. Now these three tables now we have to uh like what do you say that is post it on get it on into our
[05:58:38] database importing we know already how to import here okay the same thing I'm doing right now before going to import let me explain here here we have this
[05:58:51] these are the three tables having primary key foreign key relationship data ids see there first of all you go to customers table Nice customer data set or customers table. If you observe this customer table, you see there how
[05:59:06] what are the columns you have customer ID, customer name, customer location and customer phone number is there. This is one parent tables.
[05:59:19] Okay. So then products data set is there. products. See there now here you have got product ID. You can check on your
[05:59:31] side also. This is the product ID is there. Totally we have some 50 products or something is there. 26 products are there. Fine. Then a product name is
[05:59:43] there. Okay. A price is there. Then stock is there. Then a category is stock is there. Then a category is there. This is different categories of table. Now here we have one table called sales.
[06:00:00] We have one table called sales is there. In the sales table In the sales table guys here now see there now ordered date guys. You see there that this is the sales report. It is a child table of
[06:00:15] both customers and products. Ordered date ordered number. See their customer ID. This is the foreign key column. This is This is the foreign key column. This is the foreign key column that then one
[06:00:28] more is there customer name is there but actually we don't require this customer name column they have given for a reference but we don't require why because in the sense even if we keep nothing will happen okay but customer
[06:00:42] name is not having any foreign key so only customer ID will have that this column we can avoid it okay while importing I'll avoid it that then product code is there product name is there quantity amount is there guys
[06:00:57] please check on your side is these column names are having same names what I'm showing to you here please check on your side guys if the column names are having same names
[06:01:11] names product code product name I think it is product code product name I think it is yeah if it is same no worries got it then product code is there it is one of the foreign key from the product table
[06:01:26] product name this column also we don't required required so totally we have a 26 products so 11 customers are there I think 11 customers but here how many in a sales table how
[06:01:40] but here how many in a sales table how many sales we got it here that see there now here totally how many rows are there my here now okay here row got 11 rows are there 11 in the sense 10 products has been sold out of 26
[06:01:58] products. Yeah, find that. Okay. Now let's start importing of these things. M here. So now go to your database. Right click on the tables. Right click on the tables. Go to the table data import
[06:02:13] vizard. So go to the browse. Do it on your side mark. This is my side. Do it on your side. side. Okay.
[06:02:34] first you take the customer spot customer data set you select that is you will be getting like this go to the go to the next and here see in a table spot
[06:02:46] to the next and here see in a table spot you keep a table name as a customers what that is customers you just Keep it customers. Then go to the next then next
[06:03:00] and next and go to the finish part until you make it finish. you make it finish. Yes, one table is there here. Yes, customers is the table. We have a customer. Let's leave it method.
[06:03:15] customer. Let's leave it method. Customers is the table we are having. Yes. Second part. Second one table. Go to here that browse. Select this time products.
[06:03:28] products. Select the products table and next. And here that products data set you remove that products. remove that products. Make it products and go to the sales.
[06:03:41] Make it products and go to the sales. Okay. Here that is. Okay. This is one thing we are having. So then go for sales
[06:03:54] So then go for sales guys here sales data set is there in a date sales data set make it sales that make it whatma sales guys here guys a small modification do
[06:04:11] it here now in this s situation okay in this situation uncheck this C name column P name column uncheck those two columns. Uncheck that CN name and T
[06:04:23] two columns. Uncheck that CN name and T name column and go to the next and next and make it finish. Yeah guys, now my point is right now here let's see joins concept is joins. So now the joins
[06:04:39] concept completely we will work it on these three tables. these three tables. Yes, come to the concept now. So what is a join? What is a join or what is joins here? Now that so what is
[06:04:54] a joints in the sense maji there the process of retrieving data from multiple process of retrieving data from multiple table when those tables are in when those tables
[06:05:07] tables are in relationship that is see we can retrieve data from multiple tables but not taking from random tables that take one student table one employee table and retrieve the data from those
[06:05:22] two tables it's not possible like that. Okay. So when the table and tables are in relationship that means primary key foreign key from the tables you want some valid data you want to retrieve
[06:05:35] that in the sense then joins process can use it as you know that when you are having tables in relationship as you know that when you are having relationship tables that so definitely you will be having a
[06:05:49] common column between a parent and a child table parent and child table that Okay, that is one thing you have to remember. So whenever you observe here the tables which we have taken there. Okay. So here
[06:06:04] just see here customer ID is there in a customer's table and uh in products customer's table and uh in products table product ID product code is there product code is there. So then uh in a sales table in a sales table must see
[06:06:21] there customer ID it's a foreign key product code it's a foreign key common column is there here those two are the parent tables this is the child table you are having okay so like that so when the tables are under
[06:06:37] joining sorry when the tables are under relationship by using join process you can retrieve the data you can retrieve the data here. Yeah. How we can go for retrieving data from multiple tables using joins? This is the syntax you have
[06:06:52] to follow. This is the syntax we have to follow that. See select what that is? See since we are getting data from multiple tables since we are getting
[06:07:04] data from multiple tables here. So on which column on which table which column you want to retrieve that you need to go. So table one dot column 1 table 1 dot column 2 table 1 dot column 3 like this again another table called table 2
[06:07:20] column 1 table 2 doc column 2 like this you have to mention then afterwards from you have to mention then afterwards from table one join type table two
[06:07:32] table one join type table two table one join type table two and uh on join condition this is the process you have to take join Join condition in the sense what he get here what is a join condition here
[06:07:46] let's see join condition is a condition join condition is a condition has to be placed common column just see common column of joining tables.
[06:07:59] So that is one thing just we are taking here. Yeah. Common column that is common column of joining tables. What how the condition should go on table one dot common column equal to table two dot common column.
[06:08:16] So this is going for like this. For example customer dot customer id equal to sales doc customer id or else products.p product code equals to sales.p Product code it comes like this. While writing queries you will come to
[06:08:31] know while writing queries you will come to know here. Then what is a join type? Here we have a join types are there here. Now what is this joint type in the
[06:08:43] sensema here there are different type of joints as follows. One is my here inner join is there. Second one outer joint that again classified into two types that left outer joint right outer joint. Then we have a cross joiner. Then we
[06:08:59] have a self join. This these all joints has to discuss now. So let's go here. Now first of all inner join. Let's go for here. What that is inner
[06:09:12] join. So what is the inner join here? Whenever you go for the inner join Whenever you go for the inner join mazee, see the process of the process of retrieving rows from joining tables for only matching values are rows exa exist
[06:09:27] in common column between parent and child tables that is joining tables. Joining tables that matching rows should go here matching rows.
[06:09:41] So that's what we do here. Now let's see that how it comes right now let's see that how it comes right now there inner join how it comes there inner join how it comes one second guys so yeah
[06:10:00] so whenever you just come down here this one let's go for this is now I'm writing one let's go for this is now I'm writing one join query like this I'm writing one join see ma here customers and products are parent labels So sales is a child
[06:10:14] table. You cannot write a join query between customer and products that's not possible. Either you write customers table and sales join or else products and sales join or else by combining these three
[06:10:29] join you can write. First we write one join carry for two tables. Okay. So now so anyhow you are seeing here now see there h what is this customer id is
[06:10:42] there h what is this customer id is there now I'm writing a join query join see there see there between customers and sales.
[06:10:55] between customers and sales. Okay, this is so now first you go to Okay, this is so now first you go to write now select start from sorry
[06:11:10] Okay, suppose you want all columns from the customer table in the sense you just the customer table in the sense you just write on select customers dot star comma write on select customers dot star comma products dot star sorry not products
[06:11:25] products dot star sorry not products very sorry guys sales dot star okay that is from see there here customers
[06:11:43] customers inner joy in sales then uh on what that is customers do C then uh on what that is customers do C ID is equal to sales dot C id
[06:11:55] ID is equal to sales dot C id you should get like this here customers table when you forget that select star from here here What is the
[06:12:10] star represents here? All columns. Now I'm bringing all columns from customers table. All columns from sales table. That is
[06:12:28] customers are there here now? 13 customers are there. We have how many customers are there. We have how many customers? 13 customers are there. Okay. Now this is what we are having. Yeah. Fine. And uh you go for the sales table.
[06:12:43] You go to whatma that is sales table. You go 10 rows are there. Then uh that You go 10 rows are there. Then uh that means 10 products has been sold by uh purchased. So what is the output you get in the
[06:12:59] sense here 10 rows is the output you'll get. 10 rows is the output you'll get here. How? In the sense you take based on this one what is this based on this
[06:13:11] join condition only the rows will be retrieved okay we do one thing just see there here and copy the values of this column
[06:13:23] yeah I'm copying this so how it works I'll tell you ma how it works I'll tell you yes I added these all this is for what customers
[06:13:36] What is this customers here? Now go to the sales how the inner join Now go to the sales how the inner join works that is I'm trying to explain you take this column okay and I'm copying that and here you
[06:13:51] just paste it that is yeah and this is for what here now and this is for what here now sales it is yeah now the comparison goes either from here to here or here to here it doesn't matter doesn't matter that
[06:14:08] how it works There only matching rows will come. Only matching rows here customer ID is there. What is that? Four ones. This will be compared into the sales table of customer ID. Four ones is there. Yes, we have here. So that
[06:14:25] particular details will come. Then 1 121 you check 1 to1 is there or Then 1 121 you check 1 to1 is there or not? Yes, it is also there. Yes, it will not? Yes, it is also there. Yes, it will come. Then 1 126. Is there any 1 126? I
[06:14:39] come. Then 1 126. Is there any 1 126? I think so. No. Then 1 2 4 6. Yes. 1 2 4 6 is there. Fine. Then 1 3 1 3 is there. Is there Fine. Then 1 3 1 3 is there. Is there any 1 13 1 3? No. 1 9 1 0 is there. Yes.
[06:14:53] 1 9 1 0 is there. Then 2 1 2 3 is there. 2 1 2 3. Yes, it is there. Here. Then 3 4 52 is there.
[06:15:05] Here. Then 3 4 52 is there. Is there any 3 4 52? No. Then 3 921 is there. Yeah. 3 921 it is there. Here. See there. Here you have. Then 5334 is there. 5334 is there. Yes, it is also there.
[06:15:24] Yes, that is. Then 9021. Is there any 9021? I think so. No. 9212. Yes, it is there. First one. So then 9875
[06:15:37] is there any? Yes, here we are having 9875 is there. missing right. Huh?
[06:15:58] So 9. Yeah. Yeah. That means one product has been one customer has been done two times. That means two products might be That means two products might be purchased. Yes, correct. So now that is
[06:16:10] so what are the others that so here you see this one here. So now whenever you go for right now so this this product this customer so this this product this customer details won't come. This is cap this
[06:16:25] customer details won't come because these customers are there but they haven't purchased anything that is the simple panda that's it okay these these are skipped remaining all because matching values are there
[06:16:40] that will come there okay that is the one thing just you have okay that is the one thing just you have to remember let's see now here so go to so this one here uh Now executed this
[06:16:55] here uh Now executed this what ma execute this process. what ma execute this process. Yes. See only we have 10 rows. Yes. See only we have 10 rows. 10 rows we are having that is just wait
[06:17:53] customer ID, customer name, location, phone number. Then see their ordered date, order number, see their customer ID and what about that is product code which has been purchased this address and this is
[06:18:11] okay. So see that all the details we are getting that is but here it is not complete it's not complete informative just randomly I took all columns that okay here but the question goes like this what is the question goes in the
[06:18:27] sense here show see there show all customers who show see there show all customers who are purchased
[06:18:39] what that is who are purchased products which products purchased we don't require here who purchased the products who are purchased the products that that's it I want that information so
[06:18:56] this query I'm just taking into like this what is that here this what is that here okay customers dot you just go for okay customers dot you just go for customer name that means key name
[06:19:11] customer name that means key name that is one thing that we are having that is one thing that we are having okay so customers dot then what that is that's enough I want only those two columns that from the sales table what I
[06:19:26] columns that from the sales table what I want in the sense here I want order date want in the sense here I want order date then sales dot order number
[06:19:38] then sales dot order number okay that is then sales dot quantity what method is quantity then sales dot amount so like this you have to go
[06:19:55] what are the products they purchased we don't require that don't require that any column is missing C name actually any column is missing C name actually it's not C C name it's C name.
[06:20:08] See there guys here. See this is these customers has been See this is these customers has been done some purchasing.
[06:20:21] purchasing that is see the who is that here? Niha Kerala. Nisha from K Nisha from Kerala has been purchased has been ordered some quantity and amount
[06:20:34] like this you are having this is what guys is it understood guys this is what guys is it understood guys is this query
[06:20:52] joint compares here please let me know guys I have been explaining in Excel how guys I have been explaining in Excel how the inner join works here by copy the inner join works here by copy pasting of all these things there.
[06:21:10] uh one more what is that you know here
[06:21:24] which are sold I go for like this I don't want customers information right now I want the products information which are sold which are sold that. So then how can we go for that?
[06:21:41] So then how can we go for that? So for this here the products is there right now. Okay. So now what I'm taking my select
[06:21:56] products dop name. Okay. Then products dot price. dot price. Then products dot stock.
[06:22:11] Then products dot stock. Okay, that is then products dot uh the category we don't require. That's enough. Then go to the sales table. Sales table is what? You just take only these two.
[06:22:25] I want only these two here. Yes, that's enough. Quantity, how many quantity? What is this amount? I want that only sold because in a table we have 26
[06:22:37] products are there. You see this 26 products are there but 26 products is not sold here only few of the products only that information I want. So now here going for products
[06:22:52] inner join then sales then what method is on products dot p code is equal to sales
[06:23:04] products dot p code is equal to sales dot pers code that we need to go this is one
[06:23:27] name is unknown what happened
[06:23:55] Products is spelling wrong. Products are spelling wrong. Products are spelling wrong. No spelling wrong. Products.
[06:24:07] See that here now we got a 10 here that is so corn is the price is 50 20 stock is there 10 has been sold see 50 into 10 500 10 price is 10 rupees and it
[06:24:23] quantity two items has been sold 20 rupees total stock is like this see then only what mother is kiwi has been given twice
[06:24:35] that mean two different customers has been uh taken this kiwi might be going for like that in a table but whatever it is there so this is the product so and sales part now I want listen guys now I want three
[06:24:54] tables joining this is the two tables joining here okay guys just wait we go for that but one more point I want to discuss here simple point in this one Here every time we used to write a table name customers dot customers dotproucts
[06:25:10] dots sales dot something like that it is somewhat cumbersome one when the table names are very lengthy. So shorter names is okay. So that these queries you can write on with the table aliases.
[06:25:27] with the table aliases. For a table you can have alias name. Okay. Now for this one you can have table alias names here. Now using table alias table alias should go what is that table
[06:25:43] alias in the sensema you have to go for like this so table alias in the sense for every table you take one or two alphabets as table you take one or two alphabets as an alias name so for products I'm taking
[06:25:55] p is an alias name for products okay p dot p name then p dot price then p dot What that is stock for a sales you just to go for S dot quantity then S dot
[06:26:11] to go for S dot quantity then S dot amount like this then what is P what is S we need to tell we need to tell that so how in the sense my here products as
[06:26:23] P inner join sales as yes so it is original table name and it is alias name and it is original table name sales it is an alias name here also you can keep like that yes okay then p something like
[06:26:42] you can take see that output will be the same but see that how the code has been shortened we are avoiding repetition of table names we are avoiding repetition of table
[06:26:57] names listen carefully that alias names so alias names are the important important and mandatory for joins mandatory in the sense even you can write down with the table names also already have written at the top of that
[06:27:12] but if you come down to with this so your query becomes shortened that's it okay so I hope this is understood this aliasy names all queries you can add
[06:27:24] with aliyah's names the queries whatever we have chosen all these queries you can add with alias names only This is make it C dot star
[06:27:37] make it S dot star. So customers as C inner join sales as So customers as C inner join sales as yes then here S do C ID C do C ID
[06:27:53] yes then here S do C ID C do C ID see there with alias names it is output won't get any change guys that is so only we are making
[06:28:05] shortening of that guys please let me know I hope everyone He's able to follow the table aliases. Right? Earlier we used to take we have Right? Earlier we used to take we have been taken column aliases. Right? Now
[06:28:19] been taken column aliases. Right? Now here we are writing table aliases. So take one or two alphabets as a alias name for a table that you have to use name for a table that you have to use it.
[06:28:49] doing guys. Is it clear with aliyah's names?
[06:29:14] Yeah. Now, so here join with three tables. Three or more than three tables. But of course, we have a three tables is But of course, we have a three tables is there. I'm writing three tables part.
[06:29:26] I'm writing three tables part. Guys, listen here. Three tables. So first of all you have to make it observe that from the three to make it observe that from the three tables so two tables are parent tables
[06:29:39] one table is sales one table is sales. So when you are writing join query for a three table. So you should go for like this.
[06:29:56] So what is that? You know here select first you go to the C dot C uh uh C dot C name then C location
[06:30:08] okay that is here. Now two columns is enough for me that for the products enough for me that for the products table P do P name. Okay. Then uh what we go for the other columns here
[06:30:21] from the products table you take these all.
[06:30:41] P name has been done. P price P stock P stock quantity S quantity S amount has been taken that and suppose if you want that ordered date and all what is yeah order number ordered date
[06:30:56] also if you want you can take these two columns also like this first you define columns also like this first you define it whatever the columns you want that is Okay. All the columns from the three three tables that we took here. Three
[06:31:12] tables we took there. Okay. Let me do it one thing here.
[06:31:24] Yes. Like this. Okay. So now see there guys from customers as C inner join. guys from customers as C inner join. Okay. Sales as yes. So you cannot make I already told you you cannot make join between customers
[06:31:39] and products. Okay, that one point you have to remember. Okay, this is one thing just we go from Okay, this is one thing just we go from customer as C inner join sales as yes
[06:31:52] customer as C inner join sales as yes then on those two columns common column then on those two columns common column C do C ID is equals to yes dot C ID. So
[06:32:04] two table joining is completed. So now see the join has been done between customers and sales. Now one more join you have to make from sales
[06:32:16] and products. One more join has been done with the sales and products. How it should done here? Yes. Here again you have to write here? Yes. Here again you have to write uh inner join products.
[06:32:32] uh inner join products. Inner join products as P on see that my Inner join products as P on see that my here P do here P do P code is equal to S dotp code you have
[06:32:48] P code is equal to S dotp code you have to write like this first between customer and sales then sales and products or is first you go
[06:33:02] for products and sales then sales and customers anyh how you write it doesn't customers anyh how you write it doesn't matter complete data here now three tables join it is
[06:33:19] see there the customer stiffen is the customer has the customer stiffen is the customer has been purchased corn to it's a 50 rupees okay like 10 products he has been purchased Change the amount is 500. He
[06:33:33] has been ordered on this date to this is the order number. So complete details the order number. So complete details you have
[06:33:47] so products you just go for a kiwi kiwi twice as came because one one order from twice as came because one one order from the moan one order from Oliver that is this is order number five this is order
[06:34:00] this is order number five this is order number three you have like this Guys, is it clear guys? Writing the query join query for three tables
[06:34:24] guys. Please let me know. I hope that everyone able to follow
[06:34:46] that is first between customer and sales table and then the result of this join will again performs the join operation with the products table in yes exactly with the products table in yes exactly that's it only attach ing that is yes
[06:35:01] that's what only here you'll get the clarity on this kumar sorry karan uh next few minutes just wait okay in the next few minutes that because we have a join is there cross join there I'll explain there you'll
[06:35:16] come to know the but your understanding is fine that correct okay that's what I'm telling you first of all while writing join query first of all you have to make sure that which is parent table which is child table.
[06:35:32] You cannot write a join query between the parent tables. You have to write a join query between parent and child tables only. Because of that I took for customers and sales first. Then sales and products. First this sequence guys.
[06:35:49] First what this sequence then afterwards this sequence. Sales to then afterwards this sequence. Sales to products sequence.
[06:36:07] So please let me know anybody having any queries on behalf of this here. remember that if you're writing a join queries. So on this join queries what
[06:36:21] about other clauses we discussed earlier in select statement where clauses order by clause group by clause having clause these all can we implement yes of course we can implement the requirement should go for here now
[06:36:36] here we are just checking on only sorry join checking on only join for example let's see that is I do one thing see that is I do one thing go to this product sir Okay.
[06:36:57] report. Suppose if anyone ask that. So, that is here. Now, show all the customer and products.
[06:37:10] all the customer and products. Products of perfume category. Let's see that is perfumes category. If we write like this then how can you go? So first like this then how can you go? So first you write enter join query see there now
[06:37:24] text this join query here and after this you add where clause where it's a perfume from the products one P dot category
[06:37:36] one P dot category P dot category is equals to what should we write meth that perfume are wear clause are wear clause Yes,
[06:38:07] spelling mistake is there no
[06:38:49] correctly. No perfumes has been sold here. See there, no perfumes is sold here. See there, no perfumes is sold here now.
[06:39:01] Yes. Now you write on there. That's correct. Ma'am, no perfumes has been sold. Snacks. Now you'll get it.
[06:39:18] See there. Snacks has been sold. this
[06:39:40] examples we will write it first of all let's let us discuss all type of joins then we go for other examples including wave group by having and all that is okay just I'm showing to you that is here now
[06:39:55] that is about the inner join guys so what is an outer join here so what is an outer join there when it coming to outer join guys here now the process of
[06:40:08] retrieving rows from joining tables per matching and non-matching what that is matching and non-matching
[06:40:32] joining tables that goes here That is but again it has been divided into two types left outer join and right outer join is there left outer join and right outer join. What is
[06:40:49] this left outer join in the sense comparison from left side table left all values of left side table
[06:41:02] of left side table to right side table. Right side table in join statement. In join statement here
[06:41:15] In join statement here if value present uh in left side table. Left side table but uh not present in right side table.
[06:41:32] Excuse me. That row also retrieved. That row also retrieved That row also retrieved that is what we are have
[06:41:46] this is. So what that is left side table that so So what that is left side table that so let me write one example for this. let me write one example for this. So now here guys. So let's see that is a
[06:42:00] left order join. Take the previous examples only. So leave about three tables. First go for with the two tables. Uh first one
[06:42:12] what? Take this is the take down to the products. Now left order join. What is the question here? Now show
[06:42:24] here? Now show sold and unsold products info. Suppose sold and unsold products info.
[06:42:53] So now here so sold unsold in the sense sold so sold unsold in the sense sold products available on sales table that unsold the the products informationable products table so that
[06:43:06] I'm writing like this here okay and here I'm writing in this way what is that uh left outer join
[06:43:19] sales something like this we are having okay ma'am okay ma'am Let's see that just let's see let me take this one for an explanation prospection
[06:43:33] prospection in here this quiry you get it here that
[06:43:46] and what is the common column product code no okay get the values from the code no okay get the values from the product code from the products table. It product code from the products table. It is from the products table.
[06:43:59] Get all product codes. 26 products codes is there. Now you bring it this one is there. Now you bring it this one here. Yeah, this is the one. This is what here. Now
[06:44:12] products. Now sold products is available on sales table. See here product codes are there. You bring these all these product codes. Okay. Here you just keep it that uh
[06:44:28] Okay. Here you just keep it that uh right now and it is sales. Okay. Now how the left outer join works guys please. So find out here that
[06:44:46] one guys you see this line guys you see this line go for this line here uh for the word left outer join what is the left side table what is the right side table
[06:45:00] what is the left side table what is the right side table for the left outer join right side table for the left outer join word on that statement
[06:45:16] yeah what mother is products is the left side table sales is the right sided table is it correct
[06:45:45] the statement what I selected from the from keyword from keyword from the from keyword selected here or else you just come down here so this is what
[06:46:02] here so this is what don't confuse guys in this line. This is the left outer join, right? For that left outer join word, what is the left side table? What is the right side table?
[06:46:17] What is the left side table and right side table? Chandra, please tell me, have you cleared it or not?
[06:46:31] cleared it or not? Guys, everyone productive, right side table is what? That is sales.
[06:46:47] That is sales. So, now one second guys.
[06:47:02] So what I explained here left out order join means comparison from see comparison from all values of left side table to right side table in join
[06:47:14] statement. Here if values are present in see if values are present in left side table but not present in the right side table that row also retrieved which means now you go to Excel product
[06:47:29] which means now you go to Excel product number one the comparison should go here the comparisons should go here that is is the product number one is there in a is the product number one is there in a sales part guys tell me is the product
[06:47:44] number one is there in a sales Not. No. Though it is not having the row will come with the null values. The product come with the null values. The product number two is there. Yes, it comes with
[06:47:56] values. Product number three is there. Yes, it comes with values. Product number four is there. No. Comes with null values. Product number five is null values. Product number five is there. Yes. It will come here. Product
[06:48:09] number six is there. No. Product number seven. No. Eight. No. 9. No. 10. No. 11 seven. No. Eight. No. 9. No. 10. No. 11 is there. It will come. 17 is there.
[06:48:23] is there. It will come. 17 is there. Okay. This is then 16 also there. Okay. Then 20. What? Mother is 20 also will be having that.
[06:48:40] here. So 20 20 twice is there. Now then like this you are having matching non-matching all non-matching rows will come with the null values. So that is that means so here all values
[06:48:56] just see then here 1 to 26 all values are compared with okay now sales table present okay not present null values will come null values will come here
[06:49:09] that is so that is one thing just you have to go see that output mark left have to go see that output mark left outer join no yes see there we have a 27 rows Product number one is not there. So, so
[06:49:25] this is the sales part no quantity no amount cannot find only product table will find the details so that is coming there see
[06:49:37] totally wherever it is not having values in that will come into the nulls that will come into the null here matching non-matching both the values
[06:49:50] are coming here guys getting or not my Okay
[06:50:04] guys, is this quiry is understood or not?
[06:50:17] Guys please let me know matching non-matching. here? If it is understood then
[06:50:38] the conditions has to keep it that but just we I'm writing only core line only just we I'm writing only core line only no okay I'm writing only core
[06:51:00] uh case statement is there there we will do it that is finish the topic then we write some queries. So same thing that here suppose if it is a right outer join is what
[06:51:16] is what right outer join. left outer join vice versa to left outer join which
[06:51:30] vice versa to left outer join which means what here now the comparison goes yeah all values of right side comparison goes from right sided table to left sided table so here
[06:51:43] here I'm writing right what I'm writing my right outer join I'm what I'm writing my right outer join I'm writing here So here how the comparison goes in the sense if you go for that is the comparison from sales to products it
[06:51:58] the comparison from sales to products it goes because right side table is sales left side table is products. So that comparison goes from sales to product. comparison goes from sales to product. Sales to product in the sense
[06:52:13] where is that arrow symbol not this is sorry. Yeah. So here what is that? We get it right now. First 11. Yeah. 11 is there. 17 17 some 17 will
[06:52:29] Yeah. 11 is there. 17 17 some 17 will go. Two two will go. Then 20 20 also go. Two two will go. Then 20 20 also will go for that is one more 20 will go 16 will go and 19 also will go that is then five will go for here then 25 last
[06:52:44] then five will go for here then 25 last but one will go three will go. So all values are completed from right side table to left side table. See all values comparison of right side has been completed. There is no values to compare
[06:52:59] from right side table to left side table. So even though left side table having excess of values, it won't be compared because comparison is going from sales to product. So all values are completed.
[06:53:15] Obviously you get only sales report. Sold sales report. Obviously get what that is sold sales report. You get it. That is see there report. You get it. That is see there now.
[06:53:31] So that sold sales report only you get that. See you got only 10 rows. You got only 10 rows here. That is
[06:53:46] You got only 10 rows here. That is this is here now right outer join. sided table not present on the left side table it will bring if it is right outer
[06:54:00] table it will bring if it is right outer join. If it is left outer join value exist on left out leftsided table not exist on right sided table no worries it will bring the values along with the matching I'm talking about non-matching
[06:54:13] things. Okay. All values of leftsided table will be compared to the right side table if it is left outer join. All values of to the right side table if it is right outer join.
[06:54:38] And you change the positions of tables again the output will come reverse. there? So here
[06:54:52] now here you go for this and here what I'm taking you know and here what I'm taking you know this is I'm taking here and here I'm just going for that uh here.
[06:55:08] here. Now tell me the result. Now if I write like this what is the result guys?
[06:55:30] this quy result is asome as right after join one 10 rows. Yes exactly This you make it left out join sorry position change my here.
[06:56:03] non-matching will come that is so it is a purely depending on position of table left and right order joins purely depending on position of table that
[06:56:16] in a join statement [snorts] I hope it's clear with the left and right outer joints right right outer joints right clear or not
[06:56:42] Yeah. Let's go for another uh join. Cross join is there. What is this cross join? You know uh so
[06:56:55] this type of this join is a join process is a join process join processor without a join condition
[06:57:07] join processor without a join condition without join condition Here let's write on a query without join
[06:57:21] condition. What will happen if you write that?
[06:57:34] So take this is only take this process only here remove this join condition and here you write on join condition and here you write on cross join.
[06:57:50] here guys listen [snorts] that uh what do you say that is um yeah how many rows are there in a sales table a products table guys? 26 rows we have right? How many rows are there in a products table? 26 rows. How many how many rows are
[06:58:06] 26 rows. How many how many rows are there in a sales table? 10 rows. Okay. Now see the result of this is which one that Yeah. See the result of this one? Here
[06:58:31] what happened here? What happened here? Ma, you guessed that. What happened? What is the result of this one? What happened?
[06:58:53] 26 rows into 10 rows multiplied both tables. 26 into 10 260 rows. So see tables. 26 into 10 260 rows. So see there one product has been mapped with
[06:59:07] all 10 rows of sales table. See there now here see the result. Yes these are the 10 is there. Okay that is one thing. Then one more product then one like this we are have
[06:59:26] actually this is called here curtsine product. product. Cart sign product we can call that that means whenever you are retrieving data from multiple tables
[06:59:40] with the join process first what happens you know one result will be generated by multiplying one table rows into another table like this. table like this. Okay. Buth that is inconsistent data.
[06:59:55] Okay. Buth that is inconsistent data. From that one to retrieve actual rows we are [snorts] we have to keep a join condition. We are we have to keep what that is join condition. That is the importance of
[07:00:07] join condition. If you are not writing the join condition there is no meaning of join statement. You see here now whenever now you add this join condition here for this one you will get actual data only 10 rows
[07:00:23] you will get actual data only 10 rows you'll get see there you got the 10 rows only here even for the cross join even for the cross join so cross joiner is not useful for
[07:00:37] production it is not useful for production that it is the join has been given to is just to make you understand what's happen. That means what happens when you write a join statement without join condition in
[07:00:53] memory one table rows are multiplied with another table and one product one's output will be generated that's called output will be generated that's called curtain product from that one in that
[07:01:06] one you have a consistent inconsistent rows from that one only consistent data retrieve that join condition has to be kept
[07:01:33] condition there is no anything that except cross join. But cross join is not useful for production. Just for our understanding perspect they have given understanding perspect they have given that
[07:02:21] I'm not getting any responses from others.
[07:02:48] that's okay. Then we go for right now self join. self join. We go for what here now? Self join here.
[07:03:05] So what is a self join here? What is a self join my uh implemented on single table on single table when
[07:03:21] column to column relationship column relationship column to column relationship exist column to column relationship exist column to column relationship exist
[07:03:37] you are seeing that self join in the sense know at least two tables more than tables four tables of course four table content will not get that is but self join goes on single table only one table
[07:03:51] on one table we have one table how you write a join sir one table column to column relationship should go what that is column to column column relationship sometimes you will be having in a table one column to another
[07:04:07] column relationships are existed not only table to table. Yeah for this yes ma go to the again old table. So we are working on emp table. No have you observed that emp table ma here this employee table. Last week we
[07:04:24] worked on it. Today also we worked for the subqueries. Whenever you go for this table here you have a employee ID column is there and have a employee ID column is there and you see the last column manager ID
[07:04:38] manager ID is there these two columns are having under relationship here it is a primary key and here it is a foreign key
[07:04:53] won't mention here we won't mention here that but uh the thing is right now employee ID column same values are exist in our manager ID column to whom is
[07:05:06] working under whom that is no relationship here I I hope that these two columns are clear with you right
[07:05:21] these two columns are clear with you that so now we go for self join on this one what is the what is the occurrence will come here in the sense now see I want to know that who is working under whom I want employee name or their
[07:05:35] manager name I want like that what what I want in the sense of manager what what I want in the sense of manager name and employee name I want yeah select start from employee is that is what see the relation here now
[07:05:53] who is working under whom that I want right Okay. So for example, Roy Collins is an employee who is manager E583. What is
[07:06:05] the E583 employee name? You just bring that one here. E583. You just bring that one here. E583. Yeah. So this is Roy manager is Jet H. I want like that. I want like this Janet H manager is okay
[07:06:22] I want like this Janet H manager is okay like E002. Who is 002 here? that uh okay like E002. Who is 002 here? that uh okay like Cynthia Brooks I want like that so how you show that is yes we have to write a self join
[07:06:35] again self join in the sense there is no keyword like self join why it is calling self join in the sense because you are writing join on single table again here you have to write inner join and outer join
[07:06:56] here we have to use again whatever that is inner join or outer join that is one thing so how you take one table as a two tables in the sense because we have a table alias is there no table alias is there that based on
[07:07:12] the table alias you can do that what is that you see that how I'm writing the query here so select see there e dot first name so select see there e dot first name You do one thing make it concatenate
[07:07:27] concat of this is the function both columns I'm of this is the function both columns I'm concatenating
[07:07:39] okay that is one thing by putting comma then concat then concat of take one column as a manager M dot first name comma m dot uh m last name
[07:07:57] as sorry so here from emp as e emp as e inner join okay emp as m
[07:08:11] okay emp as m on e do emp on e do emp is equal to m dot manager you have to write query this is the self join query
[07:08:26] this is what here now self join query it is is see there now
[07:08:38] manager name this is okay you do one thing
[07:08:51] as manager manager as manager. Now you took this is
[07:09:04] see [clears throat] that Ry Collins is an employee here. Now he not given that an employee here. Now he not given that queria.
[07:09:20] is Janet Holland. Even if you want, you can check that I I Even if you want, you can check that I I removed.
[07:09:36] See Rol is an employee. 583 is what that is here 583. is here 583. Jonet Holly is a manager. So Janet Holly employee manager is what? Cynthia Brooks.
[07:09:49] Okay. Cynthia Brook manager is what? Arthur block. Arthur block themselves is Arthur block. Arthur block themselves is a that is the same thing you can check a that is the same thing you can check here. Now
[07:10:10] this is so John at Holly see Cynthia Brooks. so John at Holly see Cynthia Brooks. So last we are having this
[07:10:23] every table. There should be a relation between column to column then only it is possible otherwise we no need to go for self join otherwise we no need to go for self join guys. Is it clear?
[07:10:53] and others also ma actually here I haven't get any messages or any responses from Sri Di Svi are you there in the meeting
[07:11:11] today are you practicing or not everyone ma but actually you have responded last week no today that's why
[07:11:25] I asked you but I'm very happy that if anyone everyone will ask some questions
[07:11:38] so this is about completely joints chapter the core concept of joints but overall if you observe that Cross join we won't use remember that is one thing we didn't required self join goes for rare cases it's a very rare
[07:11:53] cases that when we are having column to column relationship between tables so that we have lef over two inner join and outer join that outer join also left outer join and right outer join that's it
[07:12:11] so that is the one just we are having I hope that overall concept is understood hope that overall concept is understood right joins concept. So here primary key foreign key depends on that joins
[07:12:25] right is it correct primary key foreign key depends on that joins is there I hope it's clear with everyone
[07:12:42] this is the today's topic guys here now okay so on this topic we'll have some queries tomorrow first uh first half an hour then we go for new concepts tomorrow okay if you have any queries please let
[07:12:56] me know tomorrow's topic I'm writing my here tomorrow's topics are here what do you say that uh views is one concept is there views is one concept
[07:13:11] views is one concept indexes is another concept then stored procedures Stored procedures is another concept we are having like this. No, no, not this one. Not stored procedures that is also
[07:13:26] will be there. Before that uh user creation, creation, user creation uh when go to the user user creation uh when go to the user creation grant comma revoke
[07:13:40] creation grant comma revoke revoke. This is one we are having. then uh roll back and commit. Okay, this is there here now. So these things first we'll keep it one thing
[07:13:55] things first we'll keep it one thing this all we keep it top side and that this all we keep it top side and that this we'll go for top side then this is the order we will make it this is the order we go for that
[07:14:17] here so in our command cell that means before going to the programming in our commands. So like we have create we have worked on create, have create we have worked on create, alter, drop, insert, update, delete,
[07:14:30] alter, drop, insert, update, delete, select only these commands we worked like one is a transaction control language command that is commit and roll back. Then second one data control language commands are there grant and
[07:14:43] revoke that grant and revoke comes under the user creation. the user creation. So that is so after that we go with the views and all that let's see that how we can go for it. Okay. So that is
[07:15:00] the very first thing here like what is a commit and roll back? What is a commit and roll back method? So whenever we go for commit and roll back the commit and
[07:15:12] roll back are essential transaction language commands just seem there used language commands just seem there used to manage data integrity by uh either permanently saving changes made uh made during the transaction or
[07:15:25] undoing them. So what is a commit here? Now the commit command used permanently to save all modifications during the current transaction to the database. Once commit statement executed uh the changes become
[07:15:42] durable cannot be undone by sub by subsequent roll back command. And what again a roll back? The roll back command used to undo all changes made during the changes transaction that made not been permanently saved by
[07:15:58] commit. This command restores the database to its state before the transaction started or before the last commit. Guys here actually yeah I think this is what we are having the key differences
[07:16:12] okay we will discuss that while working on it just come down to here okay uh this is okay find that uh
[07:16:26] so we are having so much of tables are there here okay this is one thing uh let's see that anyone table like EMP info table we'll see that so select star from EM info
[07:16:44] this is the table just we are uh yes like we have some 3M this is n of the like we have some 3M this is n of the table and none of that okay so usually guys listen carefully here usually on this table that means you
[07:16:59] take any table not only this one any table if you go that we'll do some transactions like adding new rows, modifying existing rows, deleting rows, these things. This these are the just doing a transactions that these three
[07:17:15] are called as a transactions. DML transactions we are having these transactions we are having these transactions we need to control. These transactions we need to control here. Okay, control in the sense for example
[07:17:32] you added some rows but within that rows some of the rows or all the rows made have gone some mistakes
[07:17:44] usually what we prefer in the sense wherever it has the mistake by using update command we'll correct it but I don't want that I want to remove those all rows we wanted to go for that removing off
[07:17:58] we wanted to go for that removing off all rows here that
[07:18:14] so then uh usually what we do in the sense no we go for a delete sense no we go for a delete we go for delete that that is one thing we go for delete but uh without doing that suppose you added some rows that
[07:18:31] rows is having lots of mistakes and invalid data and all that. Okay, that is invalid data and all that. Okay, that is 1 second.
[07:19:02] insertion of rows I don't want it that so that I wanted to cancel that uh transaction last for transaction I wanted to cancel just listen my point that that is one case
[07:19:18] or else suppose we did some modifications in a rose we did some modifications in a rose that but that modifications I don't want that I wanted modifications I don't want that I wanted to the previous state then I don't I
[07:19:31] wanted to cancel that transaction or else I have I have deleted some of the rows but some important rows has been removed accidentally some important rows has been removed so how to get back those deleted rows [clears throat]
[07:19:47] how to get back those deleted rows see there now this is one aspect no sir I have the data the entire thing is properly I've done it so that I
[07:19:59] wanted to made that transactions permanently in a database permanently in a database so that permanently in a database transactions in the sense either insert or update or delete you remove some rows I don't want to get
[07:20:12] back permanently I wanted to remove it yes that is one thing I did some previous state I wanted to make permanent changes in the database that is one thing I added some rows I added [clears throat] some rows to the table
[07:20:27] wanted to make permanently in the database then how so here make permanent changes on the database or make undoing the transaction just we performed
[07:20:40] so these two things are there that database will give that opportunity to database will give that opportunity to us the database the DML sorry SQL will give that opportunity for us here now that means purely on insert command
[07:20:53] update command delete command and we will make some actions. So make permanent changes on that or else make undoing of that actions. So how to do that is okay for that one we have a two commands are there commit and roll back.
[07:21:10] on the database. Roll back will make undoing the transactions. Just know previously what we performed before committing. Before committing this is very important. Once you made commit
[07:21:23] then after that if you go for undoing that it is not possible. Okay. Okay then again you have to go for other commands like delete or something that this is what but for this so if you want to use these
[07:21:37] command that means make permanent changes or make undoing that the changes or make undoing that the databases part basically see till now have done yesterday also we have done a session we created lot of different
[07:21:50] tables that but uh anyway we haven't used that commit or roll back commands why because in the sense now error. Basically, SQL will go for auto commit. That means once you add a new row, permanently added to the table. Once you
[07:22:04] delete the row from table permanently deletes, once you go for update, permanently updates that it goes this way. So, but if you wanted to use these commit and roll back based transactions, so first of all that auto commit you
[07:22:19] have to be make a switch off that auto commit option has to be switch off that. Okay, for that one there is some set command is there. That set command we have to be used. So before starting your transaction s so here we have to go for
[07:22:35] transaction s so here we have to go for set uh auto uh commit is not there here. set uh auto uh commit is not there here. So one second guys that command uh So one second guys that command uh here uh we will make it that
[07:22:50] here uh we will make it that set auto commit. So Z auto commit command here that is see there now here this set auto command
[07:23:05] part managing this one here turn on and turn off this is set auto commit on set turn off this is set auto commit on set auto this is or set auto commit off this has to be take either off or equal to zero or on or equal to 1 has to be done
[07:23:21] that is first you have to make switch off this okay switch off in the sense off this okay switch off in the sense now set auto commit equal to zero.
[07:23:35] it this easy here now or else you just go for better zero. or else you just go for better zero. Okay that is one thing.
[07:23:48] on the transactions. Now we can do okay now we can do transactions that transactions that so that is so now see there now you go
[07:24:03] to this table guys now you go to this table now I'm trying to insert some rows guys you see there that insert into emp info so that values
[07:24:18] I'm giving that something like 106 six. So then here some name. Okay. Here some department number you add some rows like this here.
[07:24:32] Okay. So 20 is not there. So that make it 30. Make it warmer. That is 30 here. Now let's see that is a
[07:24:53] see there now so now the rows are added see that here rows are added here and if you go on that one let's see that select you go on that one let's see that select star from emp info in the sense here you
[07:25:08] will get it that See that it is showing old data and new See that it is showing old data and new data both are showing here. Okay, that means it has been saved in the database table but not permanently
[07:25:22] table but not permanently it maintains. So what we can call cachy memory in the cash from the cachy memory these rows are there. So now here we these rows are there. So now here we have a chance either to make permanent
[07:25:35] these rows or else to make undoing these rows. Suppose if you don't want these rows. Suppose if you don't want these rows if you don't want these rows here simply what you need to do it in the sense roll back use the roll back. There
[07:25:49] is another syntax m simple that is and make it this is see roll back executed. Now you check whether those two rows are there or not in the database. See that
[07:26:02] those two rows are not there. So from the caching memory that rows has been removed. Just know what the operations you performed.
[07:26:14] Okay. That is so once again you insert it. Once again you inserted that. Yes. After this you must to do here now
[07:26:26] if you want to make permanent one in the sense mother error commit the commit has to go for it now commit will made that permanent changes commit will change that permanent changes on the database. Yes we have
[07:26:40] changes on the database. Yes we have that. So now see after commit after that. So now see after commit after commit you do roll back you do roll back here just go to this one and make roll back. Let's see that whether the rows
[07:26:54] will be removed from the table or not. It won't be removed. See that after the roll back after commit I did a roll back. So rows has not removed. It is back. So rows has not removed. It is there. The roll back transaction before
[07:27:09] the commit only. After the commit roll back won't work. Roll back won't work. It is like on insert. The same thing you can perform insert. The same thing you can perform on what we can call that uh updation.
[07:27:24] For example, let's say that here. So anyone name you change here any one or two names you change it. Suppose for example like see that update empame
[07:27:41] is equal to one of the student name like see I did like this here. So Rama Rama is there no ramani Ramanika. Ramanika. Okay there something like
[07:27:54] so something I did it here now but actually that listen carefully actually actually that listen carefully actually that I wanted to change this name but uh that I wanted to change this name but uh I have forgot adding that where clause
[07:28:07] see five rows are updated. So now whenever we go for this is see So now whenever we go for this is see there now here. So EM info whenever you go for this part here now see that all has game rather
[07:28:23] but we have stopped the permanent transaction so that right now here now we have an opportunity what is that made roll back.
[07:28:35] Okay. So now you see there the old data will come that see that that old data has came. old data has came. So like this we can but uh after update
[07:28:49] if you go for commit and then go for roll back it won't come. The same thing roll back it won't come. The same thing uh we do that is let's go for that uh uh we do that is let's go for that uh delete from emp info.
[07:29:01] delete from emp info. Okay you delete all rows. No worries. I Okay you delete all rows. No worries. I see I deleted all rows from the table. I deleted all rows from the table. See no rows are there. But since we have
[07:29:14] switched off that auto commit now with the roll back, we will get it that rows. So now you made roll back all the deleted rows will be back into the table. See there now
[07:29:29] will be back into the table like this. So you will be having that is
[07:29:42] following or not guys please let me know.
[07:29:54] you know if auto commit is equal to one or auto commit is on by default it is on that's why in these many days so you know we are we haven't worked that
[07:30:06] commit and roll back okay so that is what so when we added a new row it will be added permanently to the table when we modify permanently modifies when we delete permanently deletes that is it goes in this way so
[07:30:22] back commit commit and roll back first of all you have to switch off that auto commit so that set auto commit equal to zero you have to made it then only you zero you have to made it then only you can get
[07:30:40] as a one in MySQL works and data is saved permanently by default can we perform no we don't it is not possible to perform that roll back okay you have to be switch off that If we commit, if we committed deleted
[07:30:56] rows, we cannot retrieve it, right? Yes, that is what after commit you cannot go. Yes. Now in RO, let's see that one more command. Let's delete one row. Then go for commit. Then try roll back. Check that here now. So delete from EMP info
[07:31:12] that here now. So delete from EMP info where EMP number is equal to 10,7. You go for that sig details. I'm deleting right now. Yes, I deleted. Okay, that is one thing. Now you go with
[07:31:29] this is retrieve the data. Retrieve the data here. See that row is not there. But if you roll back, it will come. If you roll back, it will come here. Okay. Once you go for my roll back, see that whether it comes or not,
[07:31:46] you will come to know. Yes, I did a roll back. After that, uh just go to here. See the sick has came. Yes, once again you delete.
[07:31:59] Yes, once again you delete. Yes, I deleted. Okay, you see in the database table not there. Now made commit. Now made what? That is commit. You made it. Now the row has been deleted
[07:32:13] permanently. Okay. Now try to get the roll back. After the commit I'm doing roll back. After the commit I'm doing roll back After the commit I'm doing roll back that I did a roll back also. Now execute
[07:32:28] that I did a roll back also. Now execute here. See the row hasn't came. commit. Remember that one point.
[07:32:42] Roll back works before the commit only. after the comment roll back won't work. Do roll back works only undo the lost immediate transactions or how can I do two to three changes I have done using multiple commands yes
[07:32:56] the from there on I'm coming current just wait there on I'm coming here see guys here we did a lot of things right now
[07:33:08] okay lot of things in the sense this commit and roll back just listen this commit and roll back just listen this commit and roll back uh I been used till now on only one statement. Ive used one insert. Then I shown the difference
[07:33:22] between commit and roll back. Then I used only one update and I have shown commit and roll back differences. And I used delete and commit and roll back. used delete and commit and roll back. Okay. Now see there here.
[07:33:35] Okay. Now see there here. Yeah. Just come down to here. any table. Okay, take that EMP table that.
[07:33:49] So on this EMP table first what I did you know that here insert then uh I did now here uh some modifications. modifications. Okay. Some modify.
[07:34:04] Okay. Some modify. Then after that uh I did some delete or else I did this insert and modify. For example, that means couple of rows has been inserted and I have seen some data and on the data you modified it. Some of
[07:34:18] the rows where it has gone some mistakes but you haven't done anywhere the commit. Okay. So if I go for roll back what will happen? If I go for roll back what will happen
[07:34:32] in the sense because of we haven't done commit all rows insertion all modifications everything will go off completely wind up completely wind up completely wind up that
[07:34:45] completely wind up that or else before uh roll back if you commit that that insertions and modifications what you've done that everything will be stay in the table permanently that everything will be stay
[07:34:59] in the table permanently that then afterwards if you use roll back it won't afterwards if you use roll back it won't work it won't work that is so remember that one point so before commit whether you do row uh
[07:35:14] new row adding modifying existing rows deleting some rows or something you did it any transactions okay after that you made commit everything will make permanent okay now that is one thing or multiple
[07:35:28] transactions what you True. That is one thing. But if you get back uh suppose if you go for roll back before commit whatever you did all the transactions will be unders. That's it. That is going to be undoing.
[07:35:44] That is going to be undoing here. That is okay. It's not a part of one command or one thing that is here. I shown you one that you add multiple rows and you do you did some modifications and make it roll back
[07:35:59] everything will go off everything will go off that. So but if you make commit everything will permanently stays in the table. After you have to go for a delete under commands that is we have to go for in
[07:36:14] that way. So that you should be cautious that while working on these two commands when should when it should go commit when should when it should go roll back that it should be like what you say that is cautious
[07:36:34] got it my point [clears throat] that's about guys there is nothing to discuss about this so What we can call that uh commit and roll back that is here commit and roll back part which we
[07:36:51] are having this there is simple two commands but before that what is over here we have to make this one we have to switch off auto commit equal to zero
[07:37:05] then when you switch off set auto commit equal to one then you have to go for equal to one then you have to go for that permanently doing that is but these things permanently In a workbench you can do some permanent settings on this
[07:37:17] even that update commands and all that is so where in the sense we just wait that here we have some settings is there in the settings part we can do in the settings part we can do yes nin it's a temporary one only it's a
[07:37:32] yes nin it's a temporary one only it's a temporary one only that is that it won't be stay even suppose like see you are working on that you added Add some rows. You added some rows that suddenly your system has restarted all
[07:37:47] rows will go off. All rows will go off that. That means all the transactions what you did before committ all those transactions will cancelled. Now guys, we have come to the end of this session on SQL certification
[07:38:00] course. I hope so you would have enjoyed this certification course and also got a brief idea regarding SQL. Thank you guys for watching this video. Also guys do not forget to subscribe simply learn for more such informative full courses.