SQL Full Course 2026 — Step-by-Step Guide & Transcript

[00:08] 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:23] 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:36] 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:49] 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:02] Now before we begin our session, just a quick info guys. Simpan has got professional certificate program in data analytics, generative AI and adaptive systems in collaboration with IHFC and TI of IIT Delhi. This program actually

[01:15] 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:29] 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:44] mock interviews and career guidance. So guys, hurry up now and join the course. description box. Now before we move ahead in the session, here is a short question is what does SQL stands for? And your options are simple query logic,

[01:59] structured query language, system quality language or software query list. Please mention your answers in the comment section below. So guys here today's agenda is this one there. So already some notes is there now. But

[02:12] 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 first thing.

[02:25] 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 MySQL. These all

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

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

[03:07] is process and what is an information that? See why? Because in 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

[03:22] very important and it is mandatory by knowing or unknowing it doesn't matter that whether you know or whether not to know that every process will go for with these three steps only data process information.

[03:36] 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:51] it's a 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 to go for some work on it we have to go for some work on it

[04:08] here. Now that work we call it as a process. The work we call it as a process that after completion of process after completion of process again

[04:20] whatever the data we get again whatever the data we get uh that will be called the data we get uh that will be called as information that will be called as a information that so here the best in a generic way in a in a generic in a

[04:37] generalized a simple example is right now there constructing a house Constructing a house becomes we can call it as anse three things. What is that? What is the data for constructing house the material what you gather? So to

[04:52] construct a house what is that material initially? What is the material we gather for that? First of all, there should be a place then now sand, cement, bricks and all whatever the material we gather to construct the walls that

[05:06] [snorts] everything we can call it as a okay like what you can say raw material or also even a you go for iron steel also we gather that is by mixing of these all things we construct walls. The constructing walls is not going to be

[05:20] 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 or else the pillars will be uh like constructed and all that everything

[05:34] 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 is completed that means so a beautiful house will be constructed. So that

[05:50] constructed house is called as an information that constructed house is called as information. So this is the generalized example. Okay. And when you coming to the IT sector when you are coming to the IT sector like this like

[06:05] 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 the speaking language. speaking language it might be an English or Hindi or any

[06:19] native languages local languages Telugu or Hindi I'm a Telugu person actually okay I'm a Hyderabadi Telugu person so that uh Hindi Telu English any like Canada Tamil like whatever the speaking languages are there first what you have

[06:33] 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 So after learning of alphabets what we

[06:48] 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 to construct a sentences. So if you want to construct a sentences using that

[07:01] words. So definitely you must aware of grammar. Without grammar you can't construct a sentence in any speaking language whether it might be English or Hindi or Telugu or Tamil or something blah blah.

[07:14] 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 processor data same thing whenever you come to the IT

[07:28] 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 to the computer by the human being if you're not saying anything to the

[07:42] computer won't perform anything suppose I switch it on the computer and I got a desktop then if I sit I ideal can will it be done anything for us what what you are expecting from the computer no it's not possible

[07:58] computer is a very clever machine but when when you instruct to the computer then computer can perform that but computer cannot be performed anything by its own by its own That remember that point.

[08:15] What'sa we're writing something on screen. we're writing something on screen. You want to say anything?

[08:31] that guys. Until and unless if you have a query please send the query in a chat a query please send the query in a chat box please guys. Yeah. Thank you.

[08:44] So that is what here now. So we whenever 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.

[08:58] That's called here. Now we have to 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

[09:12] computer will give an output. So in a 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 the computer will be processed according

[09:26] to the instruction what we have given and based on that output will be given to us here. Okay this is what just going to be taken. Okay. But uh in a computer

[09:38] sector that means in IT sector there are two parts of working. languages. One is what here now? Learning programming languages.

[09:52] Guys I'm asking a simple question. What we do with the help of learning question who knows the programming language knowledge. What can we do after learning the programming languages? Suppose someone

[10:06] is saying that sir I know Python. So what can we do with the help of Python after 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

[10:20] much of programming languages are there. What can we give that one? Yeah, current jwani giving set of instruction to the computer. Okay, we have given set of instruction to the computer current but with that instructions what is the

[10:34] output you get. So giving instruction to the computer is fine that okay interacting with the computer is so the current answer and savvi answer is also correct but the thing you see uh you think in a high level way both of you

[10:50] I'm not wrong that I'm not saying it's a wrong answer it's correct answer perfectly correct that but in a high level way once you learn a programming language once you learn programming language then what do you do in the

[11:02] 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 instruction to the computer. You do this, you do this like that. Okay, that

[11:15] is fine. But doing that this task and that task and all that here. Now making softwares, making softwares that is sur also saying that one like communicating with the computer it helps us to develop the software and solve the problems.

[11:29] Yes, that's fine. Yeah, that is what so learning programming languages. So learning programming languages in the sense right nowadays whatever the sense right nowadays whatever the softwares you are using in a real world

[11:43] than in a society and by the different different organizations suppose if you go to the banker you will see completely computerized banking software computerized banking the every employees working banking relevant software if you

[11:58] 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 will be given to us. Let's see that is anywhere you go and if you go for any

[12:11] other organization they they are using the relevant softwares. So how those organizations? Is it ready made available in the market to go and their their computers in the sense? No, it's not possible like that. Then what

[12:27] will happen in the sense those all softwares has to be developed by using 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

[12:42] thing. So that uh learning programming languages used to develop applications or softwares applications or whatever that is

[12:54] softwares that that is one thing but one thing guys so while working with these softwares or applications suppose a banking people are working with banking software but this banking software is deals with the

[13:08] this banking software is deals with the 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:21] 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 mother is a securely? for mother securely and permanently

[13:36] 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:51] 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:04] 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:16] 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:31] 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:45] because it's the first session lo 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:59] 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:15] two ways know dealing with IT sector. One is about the programming languages for developing the application. Second one is database systems where we store the data where we store the data here. Let's see that I have given a simple

[15:29] 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 that everything go for what is a

[15:41] program. So programming is a set of instructions communicating to a computer how to perform a specific task that simply you can call as a a program that we can call it as a program that here.

[15:57] Okay, that's about irrelevant as per this topic because we are not 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 of tables? Why

[16:12] should we write a time table in the form of table format only? Why can't we write a time table in the form of sentence suppose in a schooling uh in a school or college or in educational systems that uh so first hour some language so second

[16:28] Monday first hour language second hour English third hour mathematics or something that so why should we write in a form of time table that yes easy to understand to get data in a structured manner fine that is to store

[16:43] data in a proper success so that we can access easily when it is required. Yes, current. Yes, all answers are correct. That is because sometimes some data not every data some data we need to present in the form of table for easy

[16:58] accessibility. If it write if it is written in the form of sentence if it is written in the form of sentence obviously what happen you know until and unless you read that complete sentence what is the information is there that we

[17:11] can't understand. But uh when it coming to the table format by seeing its rows and columns by seeing what matters it's rows and columns you can easily understand what is the task has to be done or what is the action is there

[17:27] within that fine and see there why this table format I took here in the sense so our database system stores in the form of tables our database system stores in the form of data stores in the form of table okay

[17:43] 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 same thing I have gone for that for easy representation accessing purpose table is used so database system uses the

[17:56] is used so database system uses the table format to store data so that now the continuation to this one data process information the next one we go for table so then you may come to know here sir to

[18:11] store data in the form of table to store data in the form of table. We have 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?

[18:24] Do you know that guys? Guys, do you know data in the form of table? Except databases. Except databases.

[18:38] You are all giving data as MySQL or something not that I'm not expecting that one here except databases. Yes. Naven yes that is yes exactly yes MS

[18:50] Naven yes 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 some

[19:05] 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 but Then why the major tools that okay but Then why again we are going for database systems

[19:20] 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 see that is so because of one thing that guys here

[19:35] so there are three reasons here now the very first one is file processing system very first one is file processing system so I think uh see there this we will take it file processing system will have the limitations will have the

[19:50] 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 system this is so to overcome limitations of file processing system.

[20:07] So already you you guys has been given the answer the same points see there here what are the limitations of file processing system first of all limited data stories a file process a file an excel file or

[20:22] MS access file whatever it is there that excel file MS access file always having some limitation to store the data but you think in a big manner think in a big manner in the sense of for example you take Take a bank, you

[20:38] for example you take Take a bank, you take a banking system. Suppose a bank having suppose you take a bank like ICIA bank is one bank. This ICI bank having one lakh customer just assume that having one lakh customers. So one lakh

[20:53] customers information has to store in a bank. Okay, it has been stored. These one lakh customers guys try to understand these one lakh customers is understand these one lakh customers is performing at least one transaction per

[21:07] performing at least one transaction 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

[21:23] has to be stored. Is it possible to store this entire data in a in in a file kind of Excel or MS access? Is it allows in the sense? No. It is not possible.

[21:36] It is not possible. It is not possible here. So that is one limitation of the file processing. The second one is here now security. File processing is not files are not having secured systems.

[21:49] So I can protect that file with the help of password in the sensor. But of course you can protect the data with the passwords. Okay, that is one more. But cannot open the file but I can delete that file.

[22:04] I can delete that file. I can rename that file that kind of activities you cannot uh stop that so that no security that is one thing then uh data access is very difficult

[22:20] data for example let's see that I told you like I say 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

[22:32] name or blah blah that is is it possible to get that using Excel sheet in this of it's not possible it is possible definitely it is possible but takes time

[22:45] definitely it is possible but takes time takes time that 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

[22:58] get me that last 3 month statement of my account then he has to gather the information from different sheets and all that It's very difficult it becomes it's very very difficult that and here like Swagatika is saying that one

[23:13] duplicate data of course data might be going for duplications here. So these are the major three reasons to avoid the file processing systems in organizational data. Organizational data we are talking about organizational

[23:28] data. Listen carefully that organizational data. Okay. So that these organizational data. Okay. So that these all will overcome the database systems. These all overcomes what that is database systems that here now. Okay.

[23:43] See, so these are the limitations here. Now from that limitations, the database 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

[23:58] users need to access data, access and update data simultaneous. So this is the answer for this one. DB will provide full security of data,

[24:11] allow stores large amount of data, we'll provide a query language to access or manipulate data. So these are the three reasons for that we should use database systems for an organizational data.

[24:28] So don't think that s Excel is not useful of course Excel is not useful for the organizational data. Excel is useful for personal data management for me. For example myself is one person I wanted to go for my all daily activities. So and I

[24:43] wanted to manage in in Excel sheet. It is perfectly suitable for me there. is perfectly suitable for me there. only one for me or my family like for you for your family or else a small scale organizations

[24:57] any small scale organizations is the Excel and MS access is useful perfectly useful but large scale data storage for organizations when that is I have given you only one lakh customer for IC bank but really is it is the bank having one

[25:12] lakh customers no some cr of customers is there or else you take another industry Take a social media accounts Instagram, Facebook is that Instagram details, Facebook account holders having the data in Excel

[25:27] way. It's so not at all possible that like education system, universities, school is limited but board, state board, central board, universities.

[25:40] So how much of data? See this is completely about the data guys. We are working on data completely. We are dealing with data that large amount of dealing with data that large amount of data. That is what we should do.

[25:53] Yes. Now come down to the what is a database. Now come down to the what is a database. Now come down to the what is a database here. Yes. Let's see. No, in a general way what is a database in the sense of we can say that like this it is

[26:09] 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:22] of an entity or else simply you can call it as it is in a collection of interrelated tables of an entity. It is a collection of interrelated tables of an entity. So like that uh we can say that is so the entity might be any

[26:39] 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 supermarket one medical stores whatever it is you can take that

[26:54] is that is called an entity here. So for that to how the data will be stored. See that guys for that I have taken here now databases student sorry database

[27:08] school database I took here. Now I simply know and simply roughly I just categorized like this basically school having students employees and

[27:20] 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:36] you can consider guys here now these all are tables personal info, class info, attendance info, exams info, marks info, fees info, games and sports info like

[27:48] this is there. If it is personal info then what will be the details we gather for that? See there now registration number, student there now registration number, student name, father name, gender, age, class

[28:01] admin that means admission has been taken for the class location and what is that student registration number is different from role number. Okay, that is one thing. Then when when he's joined to the class that

[28:17] 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:33] what is month and year you can say that is like you can have examination info, marks info, fees info, games and sports info like that relevantly student

[28:47] prospection and the same thing like uh we can Go for employees prospection, 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

[29:00] sense. So if you observe that as you know that your pay slips, there is a lot of information present on the pay slip, basic pay, net pay, allowances, okay like loans or whatever it is there that is called salaries info. Paylip

[29:14] part. Then when it coming to the library that books info is there members info that books info is there members info issued books info like this. issued books info like this. So now I can call it these all tables I

[29:27] can call it these all tables comes under one particular school. So school database I haven't written for all tables the column that attributes but one thing we so so this is what just we have to take

[29:43] down here. So what can we do it mother is suppose if a table is there. So table is 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:09] We're asking about what is a data set 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 database is also called it as a data

[30:22] database is also called it as a data set. Database is also called it as a data set. Here data set one particular table you can call that one. So you are table you can call that one. So you are saying that what is an entity? So yes,

[30:36] let's see the so whenever you wanted to have that have that banana

[30:49] yes guys these are the like what do you say that is welcoming questions here yeah see there as I told that one see an entity a simple definition part you can entity a simple definition part you can take down that here

[31:02] take down that here yes you see this on here.

[31:18] 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:32] 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:47] 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:01] 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 person being you and me are the persons that now tell me every human being having what are the

[32:16] attributes guys please let me know that yeah karan 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

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

[32:46] pick up an other card of a person other card of a person the entire history of that person will come professionally or personally is it

[33:00] okay so that is that person is uniquely identifying in India with the help of other card every Indian citizen is identifying with an other card uniquely okay so whatever the attributes you have given that one the same thing has been

[33:13] explained here see that entities are fundamental elements so whenever you come down to the database here you see this one whenever you come to the database So I took that school database in a

[33:28] school database. Okay. Here now. So a database is also you can call it as an database is also you can call it as an entities that is anything that so now see there we are having lot of different tables has been identified here. Now

[33:41] every table has got some attributes that is the attributes I have given here. So collection of attributes. So we'll call it as a one record. See here that is a so that is what I told you here a table is a collection of rows and

[33:56] 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 called as what that is record columns is called as a here attributes or properties

[34:11] no don't confuse that is okay that is one thing collection of attributes or collection of columns or collection of property properties will collection of property properties will become a row or a record. a row or

[34:25] record we can say that is for example let's say that here so now let's see

[34:40] yes suppose I'm taking now role number is one column then student name is another column gender is another column age is

[34:52] another column location is another column like this we have. column like this we have. So now here uh so name of table you can say that let's say that uh uh student so this is one now here I'm giving some

[35:08] so this is one now here I'm giving some values that is here now okay gender male values that is here now okay gender male is some 23 location something like

[35:23] 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:39] that attributes. Okay. That is one thing.

[35:53] 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 those all call it as here record what

[36:09] that is record you can say that is [snorts] this everything will be considered as one record or a row this is everything

[36:21] 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 101. Student name is an attribute and is 101. Student name is an attribute and its value is Ken. Okay. So, gender is an

[36:34] attribute and sorry, yeah, gender is male is its value. So, like that you will be getting here that is record or rows.

[37:19] And here one more also we can go for that here uh this is called here record we can say that is yeah this is one entity that

[37:31] completely record one record or else we can also going for this is a row you can can also going for this is a row you can call that one mother is row also you can call it that is like you have so much of student details we entered here

[37:47] so much of student details sir We enter like this then. like this then. Okay. Like what we can call the reserve.

[38:00] So like this we can go for it in this way that yes. So like you can add it this. So I hope everyone got it this one the differences between attributes and rows that so here one record of the student

[38:15] is an entity in this example. Yes, current that's fine. Yeah. So guys, please let me know. So the differences between so what is an are the records? I hope that everyone understood here.

[38:31] 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:46] session very first time. So I'm expecting an more answers from uh here expecting an more answers from uh here now please give your responses guys

[39:08] you guys are having almost 30 candidates in the session.

[39:21] this chat box. Is my session is going somewhat fast or slow or any phase is somewhat fast or slow or any phase is okay or something else?

[39:41] 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:53] 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:05] 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 [snorts] yes

[40:21] there should be some software tools there should be some software tools are 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 say in

[40:33] the sense so DBMS is there guys listen there this is what just you are having DB DBMS. So what is a DBMS here? A DBMS, a database management system, we can call that database management system is a

[40:49] that database management system is a software that manages databases software that manages databases acting as an interface for users and acting as an interface for users and applications to create, store, organize,

[41:01] retrieve, update, and secure data efficiently and consistently. handling large volumes and multiple users simultaneously. That is what a DBM

[41:13] 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 software tool for working with the databases. DBMS is the software tool for working

[41:29] with the databases. Within that working everything will come. What is that? So everything will come. What is that? So storing data, modifying data, removing data, retrieving data, lot of activities we do, lot of activities we do that is

[41:46] within that lot of activities. So more mainly four categories of actions are there. First one is creating, how to store that is one thing adding data you

[41:59] can say that one. Okay. The second one is very important one retrieval from huge amount of data from huge amount of data whatever the data you need how to

[42:12] retrieve that data very quickly that is called reading reading retrieving data. The second one oftenly we perform the third one oftenly we perform that how to modify data if required as far a students data for example

[42:41] volume that is because myself I'm speaking somewhat loudly only how about speaking somewhat loudly only how about others Guys,

[43:01] 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 simply you can say what is a database in the sense method so it's a

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

[43:31] [clears throat] 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 or adding data to the databases. That is one thing. The second one which

[43:45] That is one thing. The second one which is very important one 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

[44:00] one customer information how to get quickly. This is what retrieval process for the retrieval process. So there is some kind of technique that is one more. Then the third one is here. Now suppose if a situation comes to modify the data.

[44:12] Suppose in a bank a customer wants to update their phone number or customer wants to update their address or something else. That is one thing. Suppose a customer wants to withdraw their account from that bank then

[44:24] deleting of that customer details from that bank deleting details these things will happen here. So that simply you can call it as here code operations. What is that here? Now code operations

[44:38] is there. So C stands for here create for storing. U stands for here now for storing. U stands for here now update. Then R stands for here now read or retrieval. Then D stands for here now delete.

[44:53] D stands for what? That is delete. Mainly we focus on this. These these are the operations that but these will be done in a different different ways. done in a different different ways. Okay. Now that is what okay. So now to

[45:07] work on these databases. So so there are some rules in DBMS 14 rules is there. DBMS consist of 14 rules to create and work with databases which has been

[45:19] defined by MF card. So that those rules are called as card rules. to discuss those rules guys. Theoretically practically only we discuss that is by taking one software tool. Okay, that is okay that is one

[45:35] thing just I'm forever information I'm just giving that 14 rules will exist just giving that 14 rules will exist that okay so that is one thing so what are the database software tools guys here now let's see have you heard about

[45:47] these names guys have you heard about these names dbase and fox pro anyone have you heard about those names

[45:59] anyone have you heard about those names h would be fine. Nice. Sounds good that. So if it is because you guys are saying

[46:12] 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 database '7s or something the very first database systems in the world is DBase. Then

[46:26] modified version of that database system is right now right there. Fox Pro is there. Those are the very fundamental very initial days of database systems. Those two software tools are now also available but no one

[46:41] tools are now also available but no one is using that. Okay. Later on that we is using that. Okay. Later on that we got Oracle, SQL Server, MySQL, Postgra SQL, DB2. There are so much of database software tools are there. Okay,

[46:55] that is so we are concerned with MySQL guys. We are concerned with MySQL. But guys. We are concerned with MySQL. But before that guys listen we have a types of DBMSs is there. See DBMS has been evaluated kind of in the late 1970s or

[47:13] 80s that is exactly uh we don't need that exact year or something that is that exact year or something that is okay but right now we are in the 2026. We are in 2020 is all about 50 years lifespan is there again even if you go

[47:29] for 1980 so that is 20 years this is again 26 45 years so this lifespan is there for these things but within this 45 years there is a lot of changes has came into the software industry so that the DBMS also has been uh modified in a

[47:47] different prospections and got renamed in a different ways and added some additional features to is in that prospection. Okay. Here first modification, first recognized modification of the DBMS is RDBMS,

[48:03] relational database management system. We can say that what is meant by that relational database management system. Okay. here. So the relationship between tables

[48:16] relationship between tables for example you see this is the student table that would be fine here. Okay. Now I'm giving there here now marks table that there here now marks table that okay in this marks table I'm giving uh

[48:31] 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:43] that is whose marks it is I'm going to introduce here now a column called role number a column called role number that And let a column called role number that And let me add two more students here. No.

[49:18] number is one that then exam is for example let's say that quarterly some 45 example let's say that quarterly some 45 and again 46 so okay now 46 that so 91 is the total so whose marks it is listen guys okay here the marks of the student

[49:35] 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 four that is my role that is my rule here. This marks for the

[49:49] that is my rule here. This marks for the uh what we can say that is 1,3 let's say that is so then second one quarterly okay now so then second one quarterly okay now like 60 here again 60 some 120 is there

[50:03] this is 1,1 and you see this one guys for example let's say that here so I'm just giving that right now so in so I'm just giving that right now so in this way so 130 is the answer Suppose if

[50:18] I give 109 is this role number is a valid is this role number is a valid here guys please tell me is the role number is a valid no. So here this column this column should not

[50:33] accept the role number which is not present here since it is Excel. Okay, we are able to give. Okay, but uh as for the database

[50:45] systems, it should not act because so these two tables are not having a relationship. But we have to maintain a relation between student table of role number column to marks table of role number column. Marks table

[51:02] of this role number column that is whenever we make a relationship between these two here. So here this only this value won't be allowed because whenever you are entering a value in this column that value will be referred into these

[51:18] four values. So when the when that value is present here then it allows here. In that way we wanted to make a relationship but uh in a DBMS systems listen carefully guys in a pure DBMS systems it

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

[51:46] when the DBMS rules 14 rules has been developed that relationship concept has 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.

[52:02] So under that these two softares will work. DBase and Fox Pro will work. That 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

[52:17] relationship process rules has been added to this DBMS. Then the name has added to this DBMS. Then the name has been given for that RDBMS. Only one feature I explained here in Excel. Okay. relational regarding

[52:31] relational process that is the main feature that is the main feature and some other aspects also there so the name has been given to the RDBMS relational database management system in this relational database management

[52:45] system we got a concepts called primary key foreign key concept the foreign keys the concept has been introduced making relation between tables to avoid the inconsistent data or duplicate data or something

[53:01] Okay, that you will come to know while working on that. Okay, so that is the one here. Now RDBMS guys here now relational database management system

[53:18] 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:30] 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:44] this modern database management system in the sense is it is perfectly suitable in the sense is it is perfectly suitable for a present trend. Okay, present trend that is how to communicate multiple databases which is located in a

[53:58] different servers. how to communicate multiple databases which is located on a different servers that for example let's say that one more example I'll give you here uh yeah you

[54:11] take a banking system of multinational one suppose I say a bank is having in multiple countries multiple countries that is so how abroad customers will communicate into Indian branches or else how Indian customers

[54:27] are communicate with abroad branches Suppose if a person has been taken account in abroad they will maintain their own database system separately in their country Indian branches. So Indian customers having their own database

[54:40] customers having their own database systems in Indian branches only that is 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

[54:54] that one there is some kind of rules and rules has been developed which is called as like modern database management systems so just but as of now we don't require that just we are having that MDBMS process then one more very simple

[55:11] one that DBMS is there that is called here object object relational database management system. So I hope that some of the people who are having programming knowledge like object- oriented programming structure

[55:26] oops concepts is there which is purely comes under the programming languages that programming language concepts object to class something like we are having some kind of concepts are there so within that objecttoclass concepts

[55:42] 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:57] 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:10] occasionally uses that is our running DBMS is right now there here RDBMS DBMS is right now there here RDBMS so in RDBMS only like MDBMS will come, war DBMS will come. That is because of that uniqueness of that feature. So that

[56:24] 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 WDBMS here. Now every tool comes or WDBMS here. Now every tool comes under the RDBMS only like we have SQL

[56:39] server, you have 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

[56:54] like what the MDBMS will come that is the situations that when you are you wanted to pass data between one object sorry one database another database in sorry one database another database in the form of object then RDBMS concepts

[57:08] will come that so that is what so that apart that everything going for RDBMS everything going for here now RDBMS prospection only that is

[57:30] yeah so one thing one let's see okay

[57:43] very simple thing that This is one database database here now. Okay. You take local itself now database of ICICI

[57:56] itself now database of ICICI bank. Okay. And here now that is all customers info. Okay. That is one thing. Okay. Now you Okay. That is one thing. Okay. Now you go here now one more. Uh

[58:18] Okay. Like what we can call in the sense SBI bank guys. So all customers SBI bank guys. So all customers info. What is this one? This is the SBI

[58:30] bank database. This is the ICA bank database. So now all customers are having their account details over there. Guys, one point you see whenever you want to withdraw amount from ATMs by using our debit card, are

[58:46] you going to withdraw the amount by going to same ATM center, same bank ATM going to same ATM center, same bank ATM center?

[58:58] H [clears throat] tell me one year you also tell. irrespective of the bank, we go to that ATM, we insert the card and we withdraw

[59:12] 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. How SBI bank will come to know that? Okay. He is the ICA bank customer and

[59:29] the transaction has to come from the ICS bank not from the SBI then he will come bank not from the SBI then he will come to how it cost here. Yes. So here bankto bank communication will be there what that is bankto bank database

[59:47] communications will exist bankto bank communication will though you are doing transaction from the SBI bank ATM. Okay. But our transaction will happen only on the ICA bank. Same vice versa. Same vice versa that is. So now

[1:00:05] here this bank should access. So I bank 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 that.

[1:00:20] So how to get this different 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.

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

[1:00:46] placed in different servers. placed in different servers that so what are the rules we should follow in DBMS prospection

[1:00:59] in what that is DBMS prospection those rules all comes under the MDBMS rules all comes under the MDBMS I hope you got my point right now

[1:01:18] Guys, anyone having any other queries, guys?

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

[1:01:48] with the MySQL because we have so much of software tools are there SQL server MySQL something that is here. What is a MySQL? What is MySQL? Simple thing guys. So it's one of the

[1:02:05] to repeat everything. It is one of the RDBMS database tool. RDBMS database software tool. You can write on that software tool you can say that is here.

[1:02:20] 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 server or MySQL or anything that there are two parts. There are two parts is there. One

[1:02:37] part is SQL part. One part is SQL part. Second part is One part is SQL part. Second part is programming that is a TSQL part. Names tools but there should be a programming also inside the databases.

[1:02:53] Okay, that is one thing you have to keep in mind. That is one thing you have to in mind. That is one thing you have to keep in mind that is so that SQL all the commands will be there TSQL transacts SQL programming part will come. First

[1:03:07] SQL programming part will come. First we'll continue with this. So SQL and one more thing that this SQL is a common to every software tools whether you go for veracular SQL server postgra SQL or DB or even you go from the dbase

[1:03:25] SQL or DB or even you go from the dbase fox pro also it is common might be having some little syntax differences between one software to differences between one software to another software that two 90%

[1:03:38] same syntaxes 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

[1:03:50] see it is a universal language for databases 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

[1:04:04] company but they could not able to develop the environment for it so that develop the environment for it so that that SQL was started selling to whoever that SQL was started selling to whoever wants that. Okay. So that is but in the

[1:04:17] olden days where it was working in the sensor at command prompt sensor at command prompt per minute at command prompt that okay in that way it goes there. So SQL was using at command prompt later on and

[1:04:32] developed go environments for different companies. So actually it was developed by ANC. It was developed by ANC but nowadays so it is using no but nowadays ANC's company not using the SQL because they have been

[1:04:47] 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 environment they have added programming part and all and they released as a

[1:05:01] Oracle. Then they took that Microsoft people. Then it has been developed surrounded to that and developed one product called SQL server.

[1:05:13] SQL server that later on that like MySQL, Postgress SQL 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

[1:05:27] SQL then you get your own environment and you add your additional features for that and you can release yourself one SQL tool like that is it so SQL is full of commands commands

[1:05:42] commands commands SQL is full of commands that okay how those commands will come and all will be coming into this one here.

[1:05:56] Yes guys, these are the five types of commands you have in SQL. Now we'll come to know the commands. the commands. So how the commands will go for here?

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

[1:06:29] and deletion delete structures. The commands are three create to alter drop. Just listen the words while explaining the these commands I'll clearly tell that what is the data definition, what is the data manipulation and all that.

[1:06:45] Okay, that is then data manipulation language commands are there here. These 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

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

[1:07:14] Okay, that is then data control language commands are there. So giving permissions, withdrawing permissions between databases and all that. So grant and revoke will come there like we have totally five types of commands and

[1:07:30] totally five types of commands and 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 different topics when it coming inside

[1:07:44] of that MySQL. So topic wise the commands has to be discussed. Topic wise commands has to be discussed here that commands has to be discussed here that is okay. So then we'll come to know

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

[1:08:09] these are the commands part we entered. But we go here now environment and all 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

[1:08:24] database. 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 What is a database? See then as for MySQL collection of objects

[1:08:39] as an uh an object is a data item. An object is data item in database. What are that data items in the sense table, views, procedures, functions, indexes,

[1:08:52] 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 here database. So how to create a database? How to create a database? This

[1:09:09] is the command we have to go for that from DDL create command has to use create database database name create whatma that is database database name any database. So just we can take down here. Let's see that guys how I'm

[1:09:25] here. Let's see that guys how I'm writing here guys please follow that. So I'm taking now create space database space F 2026

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

[1:09:51] have to select that command that entire command has to be selected once it is selected you go this button button is there. Flash symbol button is there. Okay, see there now the first button the three buttons is there.

[1:10:05] Folder save 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 will get one message and green color tick mark that means that database has been

[1:10:20] 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 in the right

[1:10:36] left side panel administration you are seeing a everyone okay everyone seeing this administration beside of that only one arrow mark will

[1:10:49] be there see there 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

[1:11:05] section left side arrow mark is there. If you click that administration, if you go to here, this one schemas. Once you get this schemas here, you have some

[1:11:17] get this schemas here, you have some schema. SIS is there. Below of the SIS, schema. SIS is there. Below of the SIS, below of the SIS, right click on it, right click on it. Go for refresh. Whenever you go for refresh, the

[1:11:30] database what you created will make visible here. We'll make visible. Do it that guys. Right now do it that guys. So first what

[1:11:42] should we do? Ma you go for type the command create database database name any name you can give. I have given FB 2026 DB. You give any name test DB, demo

[1:11:56] DB, okay, self DB, anything that you can give it. I have given monthwise. So after writing that you have to select that entire command and go for clicking

[1:12:09] 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 once it is done here actually you guys are having like administration

[1:12:25] the right arrow button is there you click on this right arrow button you'll get like this and here sis This database will be there. So below of that right click on it and refresh the database what you created that will visible here.

[1:12:42] Guys please confirm that how many of you succeeded? Please confirm that how many of you succeeded that?

[1:12:56] Is everyone visible? That is yes. Yes. Chandra Prakash what happened to you? Just let me know. what happened to you? Just let me know. Is are you facing any issue?

[1:13:23] patients. So once we settled on this environment then we will keep on uh environment then we will keep on uh working on the commands. Yeah we already you already did that. Okay. So can you uh decrease the screens

[1:13:39] zooming? Can you decrease the screen zooming? Screen zoom. No. No. Maximize it first. Maximize it. No. No. Maximize it first. Maximize it. Maximize.

[1:13:54] it's not getting maximize. So go for control minus on your keyboard. Control minus on your keyboard. Hold on button. Control- hyphen button on your keyboard.

[1:14:08] You do it that. Control minus

[1:14:20] the control key and click on the hyphen button. hyphen button so that your screen zooming will decrease. Uh okay wait okay but don't do anything

[1:14:36] just wait. Now there you are seeing administration. No, in the left side uh then click on that arrow button. Uh right click on white area.

[1:14:49] White area not that one in the below sis below below of the sys. Yeah refresh. Yes you got the that is keep like this on your screen. Okay stop sharing now on your screen. Okay stop sharing now you got it.

[1:15:10] create one more database you create one more database now I'm also doing that guys you going to create database test DB like this you create

[1:15:23] database test DB like this you create one more database and execute it that one more database and execute it that create one more database and execute it

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

[1:15:49] create database the database should be identified with one name database name okay that is the name of database should be given

[1:16:01] be given I hope you got it right now refresh once again. So two databases has to show like this on your systems,

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

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

[1:16:44] introducing right now here use see how to change from one database to 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

[1:16:59] I'm doing like this what is that say suppose if I want to work with the first suppose if I want to work with the first user feb 2026 DB should go and select that command.

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

[1:17:27] 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 test DB.

[1:17:39] Okay. Then click on this one. See test DB has been released. Now see two has been released but see the highlighted part. Test DB is in highlighted part. part. Test DB is in highlighted part. That means you are working with test DB.

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

[1:18:11] will be highlighted. Now you create any tables, views, indexes, procedures or something. Everything will go on store on into in this database. on into in this database. So now my point is here use command is

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

[1:18:39] 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 the database

[1:18:53] which database you want to work. So then within that tables views and all within that tables views and all everything we have to work on it. So everyone is seeing the same results use command. Everyone seeing the same

[1:19:07] command. Everyone seeing the same results guys.

[1:19:24] Yes guys. Fine it is. So now what next? So what is the next thing we are to do it here? What next? In the sense obviously in the database actual data will be stored.

[1:19:39] Listen guys here now actual actual data will be stored in tables. will be stored in tables. Actual data will be stored in tables that. So now here we have to know how to create a table.

[1:19:54] 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 command. Okay, let's see there guys. Right now there see there next

[1:20:11] 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 what here? Now any table name should be given. Sorry create table create

[1:20:27] command. So it's a database. So that to create a database we said and here the table create table then any table name you have to give

[1:20:39] then you have to open the bracket round bracket has to open then within the bracket has to open then within the table the columns list has to mentioned. See guys when coming to this Excel sheet the today morning see role number is one

[1:20:55] column name student name is one column name. Gender is one column name. A is one column name. Location is one column name like this. So we have now some columns. But whenever you come down to this column all the numbers we are

[1:21:11] entering. Whenever you go for this one names we entering. Whenever we go for gender again text has been entered is this is again text has been entered is this is numbers again location text like this if

[1:21:24] you have a birth date date values okay birth time time values something will birth time time values something will have like this so when it coming to here see there first you need to mention the column name and it's a data type has to

[1:21:40] mentioned that means what type of data you are entering into that I'll tell What are the data types having in MySQL? Then one more is there here constraints. Guys remember that some of the things

[1:21:55] I'm enclosing within anchor brackets and here I'm enclosing square bracket. Square bracket in the sense this part is optional. If required we can use if not

[1:22:07] optional. If required we can use if not required we can avoid. But whatever I enclosed within anchor brackets that must be given that must be used. You can't avoid it. That is the meaning. So now here column table name has to be

[1:22:24] mentioned. Then list of column names has to be mentioned. Along with that the to be mentioned. Along with that the data type also has to be mentioned. like this all the list of columns whatever you want you mention it and you

[1:22:39] opened a bracket here round parentheses go for closing of that and put with semicolon okay anyhow we write right now but what okay anyhow we write right now but what are the data types are existed

[1:22:54] yes we'll come to know here the data types see them these are the different types see them these are the different data types existed in database So there is one data type called int is there. What is int here? If it is int on

[1:23:11] that column all whole numbers will be entered. 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 is not having decimal that's it.

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

[1:23:40] that. Then how can I enter the text? So text car is there of string that will be called as a 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

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

[1:24:10] date value only that while entering the date you have to go with first year then month then in this format you have to enter that

[1:24:22] then time is there time values only time also having format same regular format hours, minutes. Okay. Now like here seconds that is if you want both the

[1:24:36] seconds that is if you want both the date and time, date time will be there. 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.

[1:24:50] If not image are in a database system. So other than uh text to images if you want to store in a video or audio or something that cannot be stored to direct in a video format or audio format that should be converted into the binary

[1:25:02] format. So to store the binary data we have lo B large objects binary large have lo B large 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

[1:25:19] image also you forget about that. Okay, first we will get the knowledge on these first we will get the knowledge on these data types while creating table. data types while creating table. Okay, that is so now so how to create a

[1:25:33] table by using this in the sense of course here we will be having one table 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 for explaining about one thing what is

[1:25:47] that you know okay anyhow you got this table here this command okay see there table here this command okay see there now std name is there std name so in this std name see that is nothing but student name it

[1:26:03] 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 given and sometimes I'm giving std name okay work pair of sprint so what the

[1:26:17] difference between these two here both are entering for text only what is the difference my here fixed length and variable length variable length okay now tell me what is the so here of

[1:26:32] 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. Size of the name or else length of the name maximum 20 characters we can enter.

[1:26:49] How many characters? Maximum 20 characters more than 20 characters name 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 vinu gopopal I used let me know ma

[1:27:07] how many characters are there on that venu gopal tell me how many characters are there on the way gopal guys please respond

[1:27:19] 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 given maximum how many characters we have given 20 so how many characters are

[1:27:31] free space right Now how many characters free space 11 but okay fine that's okay here also I'm giving venual here also I'm giving venuer what makes the difference between these two you

[1:27:46] the difference between these two you know in storage not explicitly listen carefully in storage in a database storage storage even though see in car in a memory that

[1:27:58] is in a storage part in a memory though you used nine characters out of maximum you used nine characters out of maximum 20 characters that memory occupation is 20 characters only. That means whether you use utilize

[1:28:12] 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 empty and won't allow you to store any

[1:28:26] other data into that in a memory man not explicit implicit memory storage but whereas when coming to here where care of 20 maximum you have given 20 characters but how many characters you utilize in the sense nine character

[1:28:40] space utilize only 9 character space will be occupied in a memory storage remaining 11 character space will left over for further usage further data story usage that is so for one more student for example if I

[1:28:56] 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 four characters car will occupy 20 characters but here four characters space one occupied remaining 16 character space

[1:29:09] will be for other perfection so that's why it is called as car and it 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

[1:29:25] memory will be occupied by the worker. But in a car whether you use 20 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

[1:29:39] create table command with this? Shall we write down? 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?

[1:29:53] now. Now I'm working on which database? Fab 2026 DB. Yes. So within this I'm writing now create table here. Just see. So so what is that create table

[1:30:06] I'm writing that bio data is my is the table name open bracket. Even you can continuously write my but I'm writing in multiple lines. So now here I'm taking

[1:30:18] row number is the column name and int is the data type and put a comma. Yes, one column has been done. Guys listen carefully. Table names, column names must be single word. Multiple words you should not use

[1:30:34] here. Table names and column names should not go for multiple words. There 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,

[1:30:51] underscore underscore can use it. Okay, 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 web pair of 20 characters. That length

[1:31:04] is up to you. Okay. Then I'm giving here now birth date. So it's a date. I'm giving that is then [clears throat] location.

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

[1:31:31] select these all lines lines all lines you select and go for execute button. Yes I got executed the table.

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

[1:31:59] [clears throat] 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

[1:32:13] triggers is there. Forget about as of now that upcoming sessions you will come to know that. See guys. So one thing it's a case sensitive. If you follow capital letters you have to use all capitals. If you

[1:32:25] follow small letters all you go for small letters. That means in which column names in the same combination you have to use here that is there is any restriction at all. Even when you write a small letters also you see there.

[1:32:41] a small letters also you see there. So whenever you come down here create table. see that I'm writing create table emp

[1:33:15] okay that is one thing then uh we can go for that salary it is also it we can make it float or else make it int only No worries that even if it is float nothing nothing nothing to worry yes like this and I'm selecting this and

[1:33:30] going for uh executing say I got executed the table so in a test DB I'm going for refreshing all this okay you see there here so you will see here now

[1:33:43] guys you see that the small letters only so here it is capital letters that is up to group but my suggestion is that you might be asking sir which one should

[1:33:55] follow better follow capital letters better follow capital letters that's it whether writing small letters or capital letters anything is okay so what is letters anything is okay so what is another one that here now wenut again

[1:34:10] getting an error what happened ward share your screen Got it. Nice, nice, nice. Sounds good.

[1:34:30] going very slowly because you have to settle down here environment and executing queries and all that. So that is who already having knowledge be patience in a session. So shall we go for

[1:34:49] 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 fast today so that every day one itself I can't go for all at once I'm taking slow

[1:35:05] can't go for all at once I'm taking slow by one by one one by one yeah guys is it okay sur [snorts] yeah so now we have created two tables, okay, in a two different databases.

[1:35:19] Okay, 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 rows? How to add the [clears throat] rows here? This is very

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

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

[1:36:07] table name. Then to which column you want to add the data that column names we should get that is column one column two or something for that columns we have to go for values val one val 2 something we have to be entered here.

[1:36:24] This is in this case wherever you are having number sorry uh strings and date having number sorry uh strings and date values string date values and all has to enclose in single quotations single quotes

[1:36:40] 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 has to get it that is see there now we got one insert command for the buy data.

[1:36:56] So they insert into buy data. So how many columns are that column names and for that one values has to be given that is let's see now how I'm writing for this. So that is I'm going to this cloud. So first which database I'm in

[1:37:11] cloud. So first which database I'm in test DB but I'm going for uh that by 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

[1:37:26] 2026 DB first I'm taking that and I'm going 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

[1:37:41] they'll go for list of columns here. Yeah guys you see there here now insert Yeah guys you see there here now insert into bio data. Okay. Open bracket. Then

[1:37:53] column names. First column role number. Second column STD name. Third column birth date. Fourth column location. You should not have the spelling mistakes. should not have the spelling mistakes. Remember that my then go for values.

[1:38:08] 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,0001. Put a comma. Then

[1:38:20] second 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 am write down that is here now so here I am writing that gita sahastra

[1:38:40] 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 birth date guys first year then month then day we should not forget that so

[1:38:54] that here also single quotes so that I'm giving right now 20210 giving right now 20210 - 9 - 12

[1:39:06] that is one thing then location that is also a string so that here we go for that provider so entering data is not a case sensitive

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

[1:39:32] like this you see there what I'm doing you know first one uh so here Gita okay sahastra will take no worries

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

[1:39:59] row is visible on the table for us in the sense it is not visualizing it is not visualizing here. So one thing so for inserting of rows we have used so for inserting of rows we have used one command insert that is okay but to

[1:40:13] see that data is there or not in the table in the sense again one more table in the sense 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

[1:40:28] 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. [clears throat] this output should come here to you

[1:40:44] guys. Work on it. that yeah see these two seema the screen which you are seeing now try to execute that one must go to your database where the b data table is there where the by data

[1:41:00] table is there first go to that command that database by using use command then that database by using use command then write down that insert command

[1:41:12] give yourself Anything do it that

[1:41:25] current which is very simple thing that in a date format first year next month next day that's it first you have to specify the year specify the year see 2010 is the year hyphen next month

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

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

[1:42:12] in quotes. Two strings are there. One date is there. That is what within single quotes. Want to date format. So here one thing Moria always storage part like that only that you can't

[1:42:27] part like that only that you can't 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

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

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

[1:43:18] yes even you use the select command 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

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

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

[1:44:03] with one insert command multiple rows can be added that is only one row now 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 like this here so what is that in the

[1:44:19] like this here so what is that in the sense just wait uh uh yeah see there here so what I'm writing you know take this insert command write on here only

[1:44:34] write on here only so insert into bio data so now here so insert into bio data so now here again row number comma std name comma again row number comma std name comma birth date so comma location

[1:44:46] [clears throat] so then I'm writing here values use. So 1,2 then here. So praise then here now so 2012 - 0 7 - 20 so then here that is

[1:45:08] so so again what that is just I'm yeah one is done then put a comma put there comma and once again you

[1:45:22] write like this what is that 1,00 to guys wanted I'm giving to show you one uh mistake that is again already given role number. So then here so here

[1:45:35] given role number. So then here so here now I'm giving vanilla yeah vanilla I'm giving that so then here what is that in the sense what 2015

[1:45:49] here what is that in the sense what 2015 10 - 10 so then again here something like I'm giving nellor okay that is one more lurkish yes so okay that is one more lurkish yes so then one more I'm giving that here. So

[1:46:04] then one more I'm giving that here. So here,4 I have given that. So

[1:46:19] inserting three rows. Yes, see there. 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. I'm trying to execute this. See that I

[1:46:35] selected those three lines and see it got executed. That is see three rows are 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.

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

[1:47:05] See that now that all rows you were able to see space problem other is but you are having the data okay like this multiple rows can be okay like this multiple rows can be added but here one problem is coming so

[1:47:18] wanted did a mistake that to show you this is because see the duplicate role number has been given it has been accepted but it should not accept it should not accept that why why it is accepted in the sense because yes while

[1:47:34] accepted in the sense because yes while creating table while creating table we haven't discussed and done with synthesis we haven't discussed and done with the concept called constraints

[1:47:49] okay that is so no constraint so that it has been accepted the duplicates no worries that is okay just for this one this is what just we do okay so how we are writing the adding multiple rows to the table. Okay guys,

[1:48:06] same thing. When you do it that on your side do it that how to write multiple rows here by seeing that you just see that Arun joti what is that? If I use car type then need to mention the length

[1:48:22] also of course. So whether it is a car or car length must be mentioned that is compulsory. I told you know that is you see codi see this excel sheet the same thing the

[1:48:37] column specification is same thing for the length if it is a string whether car the length if it is a string whether car or car it doesn't matter

[1:48:51] guys you execute this query now execute this query only with the three rows you at least do it two two rows you at least do it two

[1:49:03] 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 members that table has been accepted but it should not accept it should not accept that okay then how

[1:49:20] that can be taken up how how can you avoid yes actually always tables has to create uh with the type of what do you say in the sense constraints only since

[1:49:32] 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 go for like this here one thing so what

[1:49:47] 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 is here now the student Name is another column like age is is another column. A

[1:50:02] is column is there. Then one more is there like gender is another column. Okay. Then the location is another column we have like location is another column we have like this. Suppose five columns is there. For

[1:50:17] this it is a data type is int. And for this here work where car of some 20 or something it is int. It is car of one only. I want

[1:50:29] to represent gender male or female. Mr. F. Then here it is where car of 10. But on these columns I wanted to put some restrictions

[1:50:42] that means in the sense some rules. What is that? you know. Okay. Here it should not be duplicate duplicate and uh should not be

[1:50:56] what my null empty that is one thing we have to go. Student that is one thing we have to go. Student name it should not be name it should not be null. That is one thing. And uh it

[1:51:10] should be it should be between uh 20 to 30 years only. Gender it should be

[1:51:23] Gender it should be should be either Y or F only that is location. By default

[1:51:38] I can hydraad like this I wanted to have some restrictions that is see for every column I have given one rule. So these rules actually called as a constraints while creating table. So there is a

[1:51:53] 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 implement on table columns. While creating table while creating table for

[1:52:08] 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 as constraints. What is the first one you have here? In the sense null see it

[1:52:22] allows to empty values in a column. It is a default constraint. nothing but even if you're not giving any constraints so one constraint will be there that is null that is one thing null in the sense

[1:52:35] 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 allow the empty values the third one is

[1:52:50] unique it will not allow duplicate values then one more is there primary key is there. Primary key in the sense guys here see that it is a combination of unique and not null constraints.

[1:53:06] unique and not null constraints. But for every table per see here only one primary key will be allowed in a table only one primary key constraint can apply for a table.

[1:53:20] Okay, that is one thing. Then check it allows to provide specific values or range of values. It allows to provide specific values or range of values. That is one thing and we will write down that default. It

[1:53:35] 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. Relationship between tables that. So that that is the relationship part you

[1:53:51] forget about as of now see null to default we'll try out right now okay default we'll try out right now okay here so now I'm creating a table now I'm creating a table with constraints so

[1:54:04] 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 of here you will get a primary key

[1:54:19] 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 should go to not null what that is not null is see it should be in between 20

[1:54:32] to 30 years in the sense check constraint gend gender should be either male or female in the sense here also go for check only by default or bad in the sense no default constraint

[1:54:47] So these constants we have to set while creating student table as of now. Okay, this is so let me write on this one here. So how can we write on that

[1:54:59] query? Let's see that in fabbook database only I am creating a table like database only I am creating a table like this. So what is that here? uh I'm going to take that yes ma that is a

[1:55:16] so I'm writing guys here now see there create table student is the table name create table student is the table name okay then here go for role number it is

[1:55:28] into now you specify the constraint after data type constraint has to be specified if we give the primary key that will not allow duplicate values and null values. Okay, that is the second one. What is

[1:55:44] Okay, that is the second one. What is that? St name. Okay, where care of 20 that? St name. Okay, where care of 20 close bracket space and not null you should go. See not null. One more constant now. Now yes gender

[1:56:03] One more constant now. 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

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

[1:56:35] single quotes okay M R again gender is equals to okay F you should give then bracket close and put a comma

[1:56:50] so that is one thing we have to write out okay then is so is It is what here out okay then is so is It is what here int. Then again now for this also check

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

[1:57:16] Then location. So it is worker of 10 or 20. So default I'm giving that hydroad. Okay then go for closing and put

[1:57:28] semicolon like this you need to write. So for every column I have given one constraint. So these constraints will be checked while inserting the data.

[1:57:41] That means while adding the new rows you see there here select these all lines go see there here select these all lines go for executing this is yes I got executed for executing this is yes I got executed the tables table creation has been done

[1:57:56] the tables table creation has been done here so on tables go for refreshma here so two tables has to be shown to us right now yes one is by data another one is student table so click on the student table Okay. Now here columns will come

[1:58:12] table Okay. Now here 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 by yourself. Also practice this. Then uh I'll show you how to go for adding rows

[1:58:27] for this. So now we know insert command. So let's start inserting the rows into the table. M. So while inserting rows these constants we will check it. Guys listen here. Let's listen. So the location you see

[1:58:44] especially when coming to this the location is where there is a default hydrobad is there. So while inserting rows if the student location is hydrobad

[1:58:56] only then you can skip the location column remaining columns you can addit column remaining columns you can addit that is suppose if the student location is not hydrobat then you add that location column and insert row. So now

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

[1:59:25] not including here okay then values so I'm giving 101 okay then values so I'm giving 101 then here um so what is that then here um so what is that kavita is the name then gender female

[1:59:41] 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 inserted successfully that is see that guys you want to see that one go for

[1:59:56] 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 discussion about that is yes we got the data you see there

[2:00:10] actually in insert command in insert command we haven't added location but we got the location hydrobat because while creating table we made that hydrobat

[2:00:22] 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 it be insert the row into the table in

[2:00:34] it be insert the row into the table in the sense so will it be inserted here as of now without doing any modification it is already Done. We got the data also. Once again I'm adding here with

[2:00:49] the same role number 101 already with the 101 role number one student is 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.

[2:01:01] See there we got an error here. See 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 error in the sense you see there. So 1062 duplicate entry 10,001

[2:01:15] 1062 duplicate entry 10,001 what it is that duplicate entry 101 it is saying that one to you already 101 student role number is there already 101 student role number is there okay it's not alter hersaden it's a

[2:01:29] 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 okay so now here I'm adding another one changing the value here know, 102.

[2:01:44] constraint constraint uh here let's let me change the name now it is morally and it is a male person but accidental I have given n but we

[2:01:58] 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 again see there row is not once again got an error that what is that see there

[2:02:13] check constraint Check constraint is violated because you have given in the place of M accidentally N so not accepting that is okay so that M has to be given then one more the age should be in

[2:02:30] then one more the age should be in between 20 to 30 years I'm giving 34 let's see that 34 will be accepted or not

[2:02:42] this time one more error we got at that see check two constraint is violated student chk2 is violated yes 34 but it should be in

[2:02:54] 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 else it won't accept okay that is so I'm giving 21 now the row will be accepted

[2:03:10] 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. constraint is working here. So you have to taken care of that once

[2:03:25] you define a table with the constraints while entering data while inserting rows that proper data should be maintained here. If any uh like data is invalid the

[2:03:37] row will not be inserted that you need to check. So now with the insert command you check yourself that with the insert command you check that is okay. So that that is the importance of

[2:03:52] constraints. So without constraint we should not create the tables. If a table is uh if in table one person have a different locations then the default will be work for that person than hower.

[2:04:10] Yeah, no worries. That suppose that means suppose another person having another a different location right you are asking more

[2:04:27] sense here multiple values cannot be inserted into one place no okay suppose suppose I have one more student is there for example let's see that is if I have

[2:04:39] for example let's see that is if I have one more student that student have a different location at that moment. What should we go? The insert command has to go for changing of that is. So what we should we go for here now you go for add

[2:04:54] the location. Okay. And here you add the location. Then what happens here? uh the location will be automatically

[2:05:06] comes to the Delhi by replacing of that hydrobad. Let's go for here now 1,003. Okay, here Navia is the name.

[2:05:18] Okay, female 21 let it be that. Okay, here. So now you go for here. Now you execute this one where yes row

[2:05:30] Now you execute this one where yes row has been inserted and here uh go for this is there see the third student will come here that is yeah

[2:05:45] student will come here that is yeah see that third student delhi has came but in the place of delhi like delhi some other location in the sensor you have to put a comma and you have to add as a string in that

[2:06:07] So I hope that it is cleared with everyone.

[2:06:19] guys. So this is today we completely discussed about how to create a 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

[2:06:32] constraints and how to add the rows into that table with the insert command, insert command with a single row, insert command with multiple rows. These are the topics we discussed today along with the introduction of databases. Today's

[2:06:47] the introduction of databases. Today's agenda is so SQL 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 do you say that is table how to change from

[2:07:01] one database to another database that we have seen okay so these all things we did 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

[2:07:15] with that commands Here we'll continue those commands. So open your MySQL workbench on local machine and you create one database

[2:07:27] how we have done yesterday in the same way you create one database. You create one database here that is so now here I'm creating one database create a

[2:07:40] So it's a local machine so that you will not have any databases. If you already not have any databases. If you already created no worries I'm creating web 2026 web 2026 DB something like how I created in a

[2:07:55] cloud same thing I'm taking that yes this database has been created then enter into that database so here you so to see that in a schema section

[2:08:07] administration and schemas is there in the schema section go with the refresh the schema section go with the refresh you will having that Feb now. Yes, Feb you will having that Feb now. Yes, Feb 2026 DB is there here. So now go with

[2:08:23] what like use command to access into 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

[2:08:37] No tables are there. Now we will create the tables. We will create the tables. So now I'm going to uh discuss some other commands. So So already discussed

[2:08:50] other commands. So So already discussed commands is what that already cu s discussed commands is what that so create database has been done. Then changing database has been done.

[2:09:07] changing database has been done. Okay. Then uh creating table has been Okay. Then uh creating table has been done. Then inserting rows has been done. Right? These are the things. Guys, please let me know any queries on behalf

[2:09:22] please let me know any queries on behalf of these all things.

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

[2:09:49] update update command has to discuss then delete command has to discuss then then delete command has to discuss then drop command And so then here alter command. Okay. Then describe command.

[2:10:05] 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. Like we have some commands. Select command is very big command guys. So

[2:10:19] there we have to uh what do you say that is lot of clauses and all that is that's is lot of clauses and all that is that's why I kept into the separately

[2:10:33] discussing uh commands that this is what just we are having so if everyone is okay then shall I start the shall we go for with the first update command ma

[2:11:06] what is an update command use? Yesterday we have seen insert command. Yesterday we have seen insert command that insert command having their adding the new rows into the table. So at the same time whenever you want to

[2:11:21] So at the same time whenever you want to modify the row values modify row values 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 used here

[2:11:35] okay that is let's see guys here so what I'm writing now this one this command is I'm writing now this one this command is used to this command is used to change

[2:11:47] row values or also allows to perform calculations allows to perform calculations on specific specific uh columns

[2:12:02] specific columns of table. Okay, that is one thing you have to go Okay, 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

[2:12:17] syntax? In the sense guys, this command has to use like this. Update the table has to use like this. Update the table name has to be taken and set here column name has to be taken equal to then here value or expression. Expression nothing

[2:12:32] 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. simple command we are having here this

[2:12:48] is one thing we go so to work on this to work on this one 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 command I'm creating one table within

[2:13:02] 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 applying the update command on that okay that we do here that is one thing so

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

[2:13:30] the table is right now marks table I'm creating so create table marks marks is creating so create table marks marks is the table name as usual guys I'm giving the table name as usual guys I'm giving role number enter and I'm applying prime

[2:13:43] primary key. So no duplicates, no null values. Then student name I'm taking S name is the column name. Then I'm taking work of sum column name. Then I'm taking work of sum 20. Okay, that is one thing. Then exam

[2:13:57] I'm taking and workar of 20. Usually I'm not applying any 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

[2:14:13] it is also should not 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

[2:14:25] column names like subject one only it is int if you want here you can give uh a int if you want here you can give uh a check constraint student marks should be in between 0 to 100 it should not accept below zero

[2:14:38] it should not accept above 100 like that you can give it okay so I'm writing now check subject one greater than or equal to zero and subject one

[2:14:52] 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 two I'm not writing my subject names like English math science or so something

[2:15:05] like that I'm just [clears throat] using that is that is then I'm giving total marks take integer then I'm giving total marks take integer then I'm giving percentage AVG R it's a

[2:15:18] 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 s four letters f i l fail four letters so car can use because we are using full length

[2:15:34] 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. understood about that query. Okay. This is now I'm creating this

[2:15:48] 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 tables of database and go for refresh mark here. Then go here that is see

[2:16:01] marks table is there. See you are getting the structure. See there now the columns you can see these columns here. Yes we have that is this.

[2:16:13] So guys try to understand once we create a table like this. Okay in this table whenever you are going to insert the rows in this table whenever

[2:16:26] you are going to insert the rows row insertion. So while inserting the rows insertion. So while inserting the rows avoid this total, average and result because this total should be calculate [clears throat] and average also should

[2:16:39] [clears throat] and average also should be calculate and result also should be be calculate and result also should be calculate. Okay. Pass fail that is calculate. Okay. Pass fail that is so that how that uh process can take how

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

[2:17:03] List out the column names which you wanted to add rows here. wanted to add rows here. Okay. Then subject one then subject two Okay. Then subject one then subject two up to only then you go for that values.

[2:17:17] So one. So here uh sur is the name. Then here quarterly is the exam. then something like 67 and 89.

[2:17:31] Okay, I'm placing comma. Let me add five rows mark here. So, exam is seen. I'm changing the marks here now. 27 it is 59 and 77

[2:17:45] 59 59 or also 49 59 or also 49 and this is okay here 29. and this is okay here 29. Okay. 90 and it is 80. Yes guys see guys

[2:18:00] here like for we are we took this one and I'm executing this. Yes, five rows are see five rows are added. So that is one thing we are getting. Let's see the

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

[2:18:24] students data is there. See total column average column and result column is empty because we haven't passed the values for that because we have to calculate that. We have to go for calculating that is.

[2:18:39] So I hope that everyone understood up to this these commands all everything we did yesterday only. Okay. Now create table date inserted data that is now come down to working on this update command.

[2:18:54] What I did that here change the row values or allows to perform calculations on 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

[2:19:09] has to fill with the total marks by adding of subject one marks and subject two marks has to go for subject one plus subject two subject one plus subject two subject two subject one plus subject two see that guys I'm writing update

[2:19:24] set to which column need to update right set to which column need to update right now total total is equal to subject 1 + now total total is equal to subject 1 + subject two That's it. Okay, that

[2:19:37] goes to that subject one column and subject two column. It respective row values will fetch and that updates onto the total column all the rows not only

[2:19:49] the total column all the rows not only five 500 5,000 it doesn't matter it will be updates over there. So select that and go for execute here. See there guys five rows are affected. Five rows are affected that.

[2:20:10] here the set is one keyword which will refer the column on which column you are updating that is that what is total here? What is total?

[2:20:22] Total is what here? It's a column name of table. See it's a column name set refers 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

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

[2:20:57] guys 156 86 126 96 170 something got it or Guys, is this quiry understood by everyone?

[2:21:09] Is that update queries understood by everyone? Guys

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

[2:21:37] 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 see again five rows are affected so check now here affecting rows that see

[2:21:51] we got the total marks sorry average 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? 2.0 zero. Why can't you go for two? Actually, one thing guys, uh here data

[2:22:04] type requirements things will be fx here. Now, so subject one data type is int. Subject two data type is also int. Subject two data type type is also int. Subject two data type is also int. If I go with like this, two

[2:22:20] is also int. If I go with like this, two is also in. Two is also in. Okay. is also in. Two is also in. Okay. So integer value with any arithmetic operation of another integer will give the integer only.

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

[2:22:47] for 2.0 this is part this is everything integer this is part this is everything integer and this is the float. So integer with float integer with float you do any operation the return value will be the

[2:23:01] float only but here we haven't get any opportunity to get the decimals see that opportunity to get the decimals see that 56x2 in the sense 78 only 86x2 only 43 exact values are getting 126x2 in the sense 63 same thing is there

[2:23:17] okay but if you have such kind of values that also will come this is there two update commands guys listen these two update commands. So updating all the

[2:23:34] rows of the particular column all the rows. But right now see that guys here one point what is that you know result

[2:23:46] point what is that you know result uh should be pass if every subjectctor if every subjectctor having a greater than or equal to 35

[2:24:01] marks. This is the condition has been given. See result column is there. In this result column pass should be filled. Pass pass when if every subject having

[2:24:16] equal to 35 marks. Okay. So that means so a condition is specifying here guys listen carefully. Condition is specifying. So now I'm writing actually the query in this way. Try to understand now. Update

[2:24:33] this way. Try to understand now. Update 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

[2:24:51] but it should not come like that. Okay. If every subject if every subject marks If every subject if every subject marks having 35 or above 35. But you see here now the first first student sur

[2:25:06] student sur 89. Okay. Pass will come. 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 go for empty only.

[2:25:22] Then 7749. Okay, filled. Then 6729. No. Second subject is fail marks is there. No pass there. 80 and 90. Okay.

[2:25:34] there. No pass there. 80 and 90. Okay. Fine. This is what. So here how to Fine. This is what. So here how to specify that condition? How to specify that condition? Here the condition has to specify it here. Now

[2:25:48] how that condition can specify? Yes how that condition can specify? Yes guys. So here where clause is there. guys. So here where clause is there. Okay. So note you can take down here

[2:26:00] Okay. So note you can take down here where clause is used to specify what is specify conditions. How it is? Let's see now. You have to

[2:26:14] 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 are the column names? Subject column names here. Subject one. Yes.

[2:26:30] Greater than or equal to 35 Greater than or equal to 35 and and subject two greater than or and and subject two greater than or equal to 35. You have to write lines.

[2:26:42] How many conditions we mentioned here? Two conditions. One condition on column one. Another condition on column two. Two conditions must be satisfied. And operator and in the sense first condition must be satisfied and second

[2:26:57] condition must be satisfied. Not only that even if you specify some other conditions in between one condition another condition and in the sense all conditions must be satisfied. All all conditions must be satisfied. It

[2:27:13] should go like that. That's it. The query. Now let's see we have a five rows okay only three rows has to get update see there now wherever the conditions are satisfied see three rows are affected is there

[2:27:28] we will check that here guys you see there here and go for that is see there 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

[2:27:43] see in second row first subject is fail marks 27 in fourth Fourth row, second subject has got fail mark 29. But this is also has to filled with fail. No, that also filled with one value.

[2:27:59] for fail. We have to write a condition for what We have to write a condition for what here fail that is. So then how to write that is one more update command. Here I'm writing there

[2:28:13] fail where subject one less than 35 subject one less than 35 or not and are because to the pass every subject should get above or equal to 35 for a failure any one subject if it is

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

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

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

[2:29:12] filled here. See there now entire table has been See there now entire table has been filled over there. filled over there. Uh yes yes I think so you all getting an

[2:29:26] error for update command right? Yes. Yes. Is that update command is like Yes. Yes. Is that update command is like safe mode something like that is giving? Yes. Yes. It's an update command is that? Yeah. Fine. No worries. Before

[2:29:40] executing the update command guys. So just one second because for security constraints update and delete command directly won't work. So for that one command has to be used here. I forgot that.

[2:29:58] So one simple command is there just wait. Yeah.

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

[2:30:23] will uh it will execute for you. This is the temporary solution. Permanent solution I'll give you. First you do this one. First you do this is because of avoiding unnecessary

[2:30:37] updations and deletions directly. It won't allow you to update the rows.

[2:30:49] 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 let's see for example let's say in this table in this table here if if I updated

[2:31:05] table in this table here if if I updated the marks for example this marks not 27 it's like for example it say like 77 or 47 or something then automatically uh this total average result should get reflected but here Automatic updations

[2:31:21] are not possible here. Automatic updations are not possible. Remember that. Okay. Here automatic updations

[2:31:35] are not performed in MySQL. Remember that. For example, see, so how can I modify this marks? For example,

[2:31:48] I'll show you now. How can I modify only this marks? Can I get like this here? this marks? Can I get like this here? Now let's see update marks setup

[2:32:00] subject one equal to let's say that it is a 47. We cannot go for like this. If you go for like this all subject one marks will get 47. Only for the student we should go. So that compulsory you need to

[2:32:13] change the you need to go for a condition where condition where role number is equal to one or two. Will it work or not? See there whoever having a

[2:32:34] 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 total marks are same average marks are

[2:32:50] total marks are same average marks are same and fail result also same it is not 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.

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

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

[2:33:35] 475. So that 6 it is a 53% pass. We got it

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

[2:34:03] guys one thing I wanted to change see this is the quy guys here now change the this is the quy guys here now change the student name as like Chandra Babu

[2:34:22] uh who are having a role number who are having role number is here uh like five. having role number is here uh like five. Now tell me the query here now whose role number is five that is for that question what is the answer I

[2:34:37] that question what is the answer I wanted to change name as a chendra babu 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

[2:34:56] given answer. And what about others guys? Please.

[2:35:08] into the double quotations. Navin Navenra that should be number so that 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

[2:35:22] quotes. For Chundrau also it should be single quotes not the double quotes. received is correct. The little correction on naminra

[2:35:38] quotes and for number no need to have any single quotes. that. See there already given that uh some of the learners.

[2:35:53] some of the learners. So update mark set to yes name is equal So update mark set to yes name is equal to chandra babu to chandra babu where role number is equal to 185

[2:36:06] where role number is equal to 185 you should get like that. execute it that's about update command guys. So how

[2:36:22] the updations will be done here? How the updations will be done? That is one thing we are having.

[2:36:37] I'm moving on to some other command guys here. So what is another command in the sensor here? Delete is the command. Delete command is there. This command is used to delete

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

[2:37:11] here. So now the command syntax will come like this. Delete from table name. Delete from table name. Okay, this is if required a where clause

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

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

[2:37:54] suppose if you execute this one. If you execute this it will delete all rows from table. All rows empty table will give you

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

[2:38:21] 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 the sensema here? It will delete.

[2:38:35] the sensema here? It will delete. It will delete all rows whose result is fail. Whoever failed those all students will

[2:38:47] be deleted. We have only one failed student is there. No, I'm executing this. I'm not executing the first one because deleting rows. No, if you delete we have to insert and update everything has to be go for that. Yes, I executed.

[2:39:02] You see there one row affected. 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

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

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

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

[2:40:04] for my 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

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

[2:40:36] 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 column that is one thing or else I

[2:40:52] 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 delete a column I want to remove the column itself that is one thing I wanted to

[2:41:09] rename the column column name I wanted to change column heading column name I wanted to change like we have so of things are there. Let me write on that. So here, so this command is used to perform

[2:41:27] so this command is used to perform different uh uh modify operations. different uh uh modify operations. Modify operations on table structure not on table rows, table structure. Table structure in the sense columns

[2:41:42] that is one thing. So what is its syntax in the sensema alter table table name and here we have some keywords is there add modify

[2:41:57] drop here we have to write on expression. This is the generic syntax of that alter command. What are the actions we do here? You know first one adding new columns.

[2:42:13] know first one adding new columns. The second one is deleting existing columns. This is the second action we can do it This is the second action we can do it here. Third one here like modify

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

[2:42:38] columns. Existing columns. That is one thing. Existing columns. That is one thing. Then the fifth one is here. Dropping a primary key. If you don't want, we can drop that. Then the sixth

[2:42:53] one here renaming renaming existing column names. existing column names. These are the different six operations we can perform

[2:43:05] different six operations we can perform by using of alter statement. Yes. To do this work what we do right 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

[2:43:21] it here. Okay. So that is because to implement these all while creating table 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

[2:43:36] let's go for here now alter no here yes alter command so now alter no here yes alter command so now create a table okay now sample is the

[2:43:48] create a table okay now sample is the table name let's see that s it is in and I'm giving the not null I'm not giving wantedly primary key Okay, not giving wantedly primary key Okay, not null later we add that then yes name

[2:44:05] 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 So that is one thing. Now let's see here then four uh yeah city

[2:44:21] then four uh yeah city it is work of 20 or 10 or something something okay fine with these three columns so columns so I created a table let's see now yes

[2:44:35] so now whenever we go for guys here you just see there that 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. Yeah. Fine. And see guys

[2:44:52] here there is a command called describe is there. There is a command called describe is there. What is the describe command? You there. What is the describe command? You know that.

[2:45:08] Describe command. No, no, no. It allows to show structure of table. Structure of table that. So how this can be taken up in the

[2:45:22] 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 ribe is an optional. D also works

[2:45:37] here. Let's [clears throat] see now. There we created a table node. So, D E S C R I B E. So, sample and if you go and execute this guys, you see there, see it shows the structure.

[2:45:53] Structure means what? Column name, data type, any constants you have applied, those all will come into the here. Now, see there now field type not null or

[2:46:05] null can any keys are there primary key, foreign key something. Have you set any there for any column default 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

[2:46:22] table you can see that is not only that see there now so this is one now 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

[2:46:37] that so not null not null has been given so no null remaining all or yes has been so no null remaining all or yes has been given that is like this you can see what we can call my structure of table okay fine now we are focusing on this now

[2:46:52] let's see one by one I'm taking mat add a new columns a new columns 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

[2:47:08] add to which table to the sample table. These all are coming working with the sample table only. So let's see that how sample table only. So let's see that how we write that alter table sample see add

[2:47:24] first phone number I'm taking only phone work for 10 yes one column I'm adding here just see there now simple there is no different

[2:47:38] action here no after add you have to keyword the column name new column name and it data type that's 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

[2:47:52] yes ma'am so column has been added that but how can I see that you have to check with the describe only see own has been added here

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

[2:48:18] I'm adding that also. Yes. You see there that here. Yes. Pin code like see there the columns are extending here. Now

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

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

[2:48:59] 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 of expression in the

[2:49:15] have written here of expression in the syntax because every aspect is going to syntax because every aspect is going to be different uh writing of command be different uh writing of command drop column column name

[2:49:27] phone column I don't want yes it has been executed go for once again it has been executed go for once again describe here see no phone is there

[2:49:39] that means no phone column is there that has been removed that has been removed that okay so that is one thing here we are checking then one more so here you see that

[2:49:55] whenever you come down I want to change the data type of this s name column what is that car is there I want to convert into the work convert into the work so that change the data type of yes name

[2:50:10] so that change the data type of yes name column two were care of 20 something like this with what we can call that not null

[2:50:23] with not null constraint suppose if I write like this the query how can we go for that very simple that guys alter table sample see modify

[2:50:38] table sample see modify column what is the column name yes name. Okay. What is a new data type? You just give it where car of sum 20 then not give it where car of sum 20 then not null. Something like this. That's it.

[2:50:53] Here it is a null constraint is there. I added the notnull constraint. Select this and execute. Now this one. Yes, it executed successfully. That is Yes, it executed successfully. That is right now there. Go for executing that

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

[2:51:22] So how the structure we are changing that is we are not working on the data we are working on the table structure. Table working on the table structure. Table structure that is then we haven't added

[2:51:35] the primary key here. So add one more mother that is primary key constraint. mother that is primary key constraint. Add primary key constraint to to SNO column. Then what should we write here? Uh yes

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

[2:52:07] primary key? Yes name column sorry yes number so that you just see this one here yes it has been executed

[2:52:20] whether it is added or not how you'll 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

[2:52:33] is primary key that one part of we are having existed if you're doing anything modification that not that one it is add

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

[2:53:05] I don't want primary key I don't want primary key in the sense so actually the column so remove that primary key to the existing column then add primary key to

[2:53:17] any other column which is suitable one such kind of things if you want to do how to remove that a primary key here in the sense just see there delete a primary key delete primary key simple thing that

[2:53:32] how to delete a primary key here. Alter table sample drop. What meth drop? Simply going for that primary key. You no need to specify the column name because as I already

[2:53:48] 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 the primary key to which column it is there for that it will be deleted.

[2:54:04] there for that it will be deleted. See there now yes it got executed. Okay. So how can we see that whether it is deleted or not? See there no P here. Previously it was P that is primary key. Now no primary key

[2:54:23] that is primary key. Now no primary key like this. So we are having this one. This is the one just we go for it right now. And finally we go for like this. What is that? Rename a column

[2:54:38] that? Rename a column from S name to std name. from S name to std name. Rename a column from S name to std name. S name column want to change the std name.

[2:54:53] So then how can we go for that is alter table sample rename. table sample rename. that I would have told you that yes name

[2:55:05] that I would have told you that yes name into std name I think so that is

[2:55:18] what happened to this is rename column yes name

[2:55:36] see this one here. So now the column name becomes here. Now std name. See name becomes here. Now std name. See there.

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

[2:56:05] Drop command is there. Now okay. What is the drop command here? The drop command goes like this. Uh this command is used to delete any object

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

[2:56:36] So 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 any of these things. So

[2:56:50] then in the middle of that we'll be having some indexes will come there then to work on here the next coming sessions. Okay. So these all and that to

[2:57:02] even database. So these all are here now as a part of 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.

[2:57:18] this drop object. Okay. Then here object name object name Okay. Then here object name object name that suppose to delete a table to delete table what should we write to draw table table

[2:57:31] name in a simple one that is suppose to 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

[2:57:45] name database This name you should get like this. If it is a view drop 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

[2:58:01] drop index index name. Okay. Like this it goes here. And now uh earlier to this so we discussed about delete command. Earlier command. What makes the difference between a delete and a drop in the sense

[2:58:18] delete will delete the only rows but table will be existed. You delete all the rows but table is existed but drop is not like that. Once uh you use the drop command entire table will be deleted.

[2:58:33] Entire table will be deleted here including data. Table deletion is including data it will be deleted. That is one thing. And one more point here.

[2:58:45] So whenever you delete some rows in a table, whenever we delete some rows in a table, whenever we delete some rows in a table, what must sir? How to get started with MySQL workbench in case practice lab not working?

[2:59:02] Okay, just I'll take to your screen. Just wait for a few minutes. So here 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

[2:59:17] and commit commands is there at the at that moment I'll show you that that means there is a chance to delete to get uh there is a chance to get back the deleted rows but once you delete the table that we cannot get back

[2:59:31] that we cannot get back here so that drop command hand will delete the table permanently. You cannot revert it. Whereas if you delete the rows from the table, you delete the rows from table.

[2:59:47] 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 wear clauses or something else there here. I'm not deleting any dropping any

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

[3:00:14] I'm just going for this. Just 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.

[3:00:29] Now truncate there is a command called truncate is there. Truncate command says this command

[3:00:41] Truncate command says this command is used to delete all rows from table. is used to delete all rows from table. All rows from table.

[3:00:55] trate? This one you go for that trate. table table name like this. You should get that.

[3:01:08] It won't delete the table. Listen carefully. It won't delete the table. It carefully. It won't delete the table. It will delete all rows from table. Delete all rows from table here. Now what makes the difference between delete

[3:01:23] and truncate? So delete and truncate the difference delete and truncate.

[3:01:37] Delete and truncate in the sense let's see that delete allows to delete allows to delete specific

[3:01:55] rows based on condition. Okay. Along with the all rows. Along Okay. Along with the all rows. Along with what? Yeah. All rows. with what? Yeah. All rows. That is one thing. Whereas

[3:02:12] whereas truncate truncate deletes all rows. That is the first point. Okay. Point number one. Second point.

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

[3:02:51] is not these two points. You have to remember that is these two points has to be remembered. delete all rows. If you skip the wear clause you can delete all rows that.

[3:03:06] clause you can delete all rows that. Okay. And whereas truncate always going for all rows deletion. Truncate is going for all rows deletion that is. And you rows if any important rows has been deleted with the help of roll back

[3:03:20] 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 here. That is the

[3:03:35] 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 You no need to practice these things my here but because the data what it is

[3:03:50] there I required that for next coming examples that is so here you go for the examples that is so here you go for the list of what that is truncate what that is truncate command is there. So I hope that these all commands are

[3:04:07] completed right. No need to practice drop and a truncate. Just remember that is whenever it is required then you can go.

[3:04:20] I hope that the difference between a drop would delete to truncate understood drop would delete to truncate understood or not. Drop command will delete entire table. But other objects

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

[3:04:53] of rows based on the condition and you have a chance to get back those rows. We noted to discuss the roll back. we'll discuss just wait for navindra okay commit to roll back commands has to discuss combinationally just wait for

[3:05:08] that I'll explain it okay first you just follow these all this is then so these are the just differences then so these are the just differences between drop delete truncate

[3:05:29] some other command called select select. 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 it works. Okay the next command is guys here select command has to go

[3:05:45] 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 proper data we need proper data

[3:05:57] 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 okay everyone has been downloaded that SQL data sets right downloaded now

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

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

[3:06:34] and you have here emp 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

[3:06:50] there here you can find some sample data Now this data we wanted to bring it on Now this data we wanted to bring it on to our database.

[3:07:02] Okay. This is now this data we want it right now. So what I'm doing right now you know here uh what do you say that is? So this is what type of file in the sensma it is CSV file. You just see there that is. So

[3:07:18] see the 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 wanted to importing to our database from

[3:07:36] CSV file to here from CSV file to here we wanted to import that so now I'm performing one small task Just follow everyone and try to do it that you have to

[3:07:52] So now I want the table. Go to the tables guys. See there. Go to the tables and right click on tables. Right click on tables. You will get an

[3:08:05] Right click on tables. You will get an option called table data import vizard. Table data import vizard is there. You click on this. So you'll get here. Now go to the browser

[3:08:18] on the browser and the browse go to the where that file is there. So in my system it is in simply learn and SQL 26

[3:08:31] simply learn and SQL 26 and SQL data sets assisted data sets. Go for lesson five and I'm selecting this is selecting the DMP table and go for open it.

[3:08:43] it. 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

[3:08:58] name will be taken here. But you go to EMP that is remove the table. Just keep EMP that is remove the table. Just keep the emp

[3:09:13] see it is showing entire data here columns and its data everything it is showing to you here now the data is coming right now there then next okay simply go for next and next next go

[3:09:28] for finish that's it ma the table will have here just see that go for refresh see you have a EMP table. You have a EMP table that separate file you took. And you see there guys, right

[3:09:44] you took. And you see there guys, right now select star from EMP. now select star from EMP. We imported data from CSV file into our database. See that we got the data entire data. We got it here.

[3:10:04] because select statement has got a different clauses so that some proper 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

[3:10:22] guys do you want me to show once again or else will you do it that

[3:10:40] on the tables of your database and go to the table data import wizard. the table data import wizard. Table data import vizard and here it will be empty actually. Go to the browse and where that file is there that you go

[3:10:55] to. So SQL data sets assisted practice data sets lesson five and select that 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

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

[3:11:26] because of that I'm not continuing that yeah so I hope that everyone has imported that EMP table, right?

[3:11:39] that EMP table, right? Everyone imported EMP table from CSV Everyone imported EMP table from CSV file to your database.

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

[3:12:07] has different clauses and all that is there. Let's go for one by one here. So how we can go for that is so first of all the purpose of select

[3:12:19] command is here to retrieve data from single or multiple tables. we already working on uh previously also. That mean select start from table name or something that the completely how you get that is you see there here see that

[3:12:36] this command is used to retrieve data 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

[3:12:51] discuss now so select and here expression should be there 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

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

[3:13:26] 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. Have you observed that table data here? Employee ID is

[3:13:42] that 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,

[3:13:57] continent is there, salary column, employee rating, manager ID like 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

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

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

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

[3:15:03] that employee. 583. Yes. See there. So Janet Halle female can she's a manager manager for what? Retail department. See so Joy Collings is an employee of senior sat scientist working in retail department. For the department Janet Hal

[3:15:18] is the manager. Okay. 14 years of experience living in Colombia South America taking 10,000 salary. Okay. Now employee rating is salary. Okay. Now employee rating is two. But she's the manager. But she's

[3:15:32] also having next reporting manager. Who is that E002? Who is that E002? You have to search that E002 here. Yes. Cynthia Brooks

[3:15:44] female president department is all in one that 17 years of experience Canada North America 14,500 is taking rating is five. See she president also has to

[3:15:56] report to someone. Who is that? E001. Who is 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

[3:16:11] report any other person. Himself is a reporting person. That is one thing. 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

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

[3:16:41] this data we'll make some queries as of now so select star from emp can you tell anyone that what is meant by star here what is star represents ma

[3:16:55] what the star represents Somebody having some already knowledge Somebody having some already knowledge no for those people I'm asking that h it's not complete data all columns are correct all columns

[3:17:11] column column representations that is all columns column representations that all columns column representations that is so all columns you can go for that is

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

[3:17:40] sometimes I want only specific columns data here then how to write a query in the sense so if 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 Then select so I want

[3:17:57] go for that is Then select so I want first name so that first underscore name first name so that first underscore name then comma so then job is there ro then dept okay then salary so from emp

[3:18:13] 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 right No visibility is what that is four columns visibility we are having that

[3:18:28] is. So that is one thing just uh we are getting right now like any number of columns any sorry any columns which you can take down that that is not an issue here see all table that is now here we

[3:18:46] can perform some calculations over there here now so calculations 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

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

[3:19:18] what I want here. Now yearly salary I want it there. Then how that yearly salary should go here. Okay. So now see there select

[3:19:30] 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 write like this salary into 12

[3:19:46] write like this salary into 12 doing some multiplication there. So then doing some multiplication there. So then from EMP you can take that is

[3:19:59] monthly salary that is and salary into 12 in the sense 84,000 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

[3:20:13] this column is not present remember that this last one is not present on the present on the table. No, for temporarily selected statement is generated that column for showing the

[3:20:26] result. That's it. We're showing the result here. But 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.

[3:20:42] But here one point is there. Now whatever the formula you have written 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

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

[3:21:12] that we are having here column aliases what is that column aliasis is there. what is that column aliasis is there. Okay. So this is what the column alias. So it it is an alternative name for the table columns or expressions of select

[3:21:25] statement only. Remember that how you go for that is here. So select specify the column name or expression for that one as alias name has to

[3:21:37] specify it. Alia's name has to specify that is see how you write a query for that mark see there now I'm taking right now the selector first name as a emp name see I'm

[3:21:55] first name as a emp name see I'm changing the name of that role as a designation then salary as a monthly

[3:22:08] Sal. So then salary into 12 as yearly sal So then salary into 12 as yearly sal then here salary by 30 as of day wise

[3:22:23] sal okay from emp emp name role has been taken as a designation salary has been taken as a

[3:22:38] physical column names remaining two are the calculations I did that and I'm the calculations I did that and I'm going for that see there now going for that see there now see there this is 7,000 is the see there

[3:22:52] first of all see the heading employee name emp name designation monthly sal yearly sal day wise sal something like you are having them just temporarily it's not we are not changing the column names physically in

[3:23:07] a table physically In a table column names if you want to change you have to 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

[3:23:20] the headings we need that we are getting that's called as an alias column aliases what is that column aliasis you can write on here so this is one thing so guys here first what we're taking all columns listen

[3:23:37] carefully that the Second part is right now 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 perform

[3:23:52] 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 designations salary as a

[3:24:08] monthly sal 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

[3:24:25] distinct clause is there. So there is a clause called distinct clause. What is this clause in the sense? See this clause shows only actual or unique values of specified column by avoiding duplicates.

[3:24:41] What it means in the sense map you just see there. see there. For example, if you take some columns now see there now you tell me how many job roles are there here. Now how many

[3:24:56] 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 sorry like four is there right now.

[3:25:13] sorry like four is there right now. Okay. Senior, junior, associate. Let's see some other one here. Lead data scientist four.

[3:25:25] Lead data scientist four. Manager is five. President is six. CO is 7th like you have that and even if you come to the department column you have a deep uh retail department one department finance is

[3:25:39] another department automotive is another department healthcare is another department yes that's it four departments are there all five departments and even when you come to the here now

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

[3:26:06] female. I wanted to see that is now tell me see there guys here. Select now tell me see there guys here. Select DP from EMP. If I write that what is the

[3:26:18] output of this query? Select DP from EMP. In this sense what Select DP from EMP. In this sense what is the output of this query?

[3:26:36] All values of that d column will come. Right? Only one column dpp will come. Right? Only one column dpp will come. All 20 values will come. Right? All 20 values will come there. See there. Let me execute that.

[3:26:58] is okay this is one but I don't want that duplicates I want actual values very simple thing that just add a distinct

[3:27:10] simple thing that just add a distinct dbd from emp 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

[3:27:25] prospection in a table. It is there we are not disturbing anything table only retrieval prospection. See there now five departments we have like you have other also suppose like a distinct gender

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

[3:28:09] 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 quiry is correct.

[3:28:25] That query is correct that. So then here now country from EMP actual countries will be showing to us here. Now see

[3:28:39] 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 now. Is it clear or not?

[3:28:53] like you have some guys here uh just let me continue the explaining this anyhow you have a table ready so now this queries you can

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

[3:29:17] statement what is that section here select clone this section is complete whatma in this expression area we got three sections there. One is all columns

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

[3:29:49] alias names that is second one. Third one is providing distinct clause to see one is providing distinct clause to see actual values from specific columns. Okay, that is one thing we are having. These are the things. So we should go

[3:30:05] next. Now another clause is there where clause. So where clause we already discussed for the previous two commands for update and delete. In update command where clause used to modify the specific row values based on

[3:30:20] 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 that is. So that is what here where

[3:30:34] clause. So it is used what method is used to specify what method is used to specify conditions on table column to retrieve rows to retrieve rows. That is how it should

[3:30:47] go. Select blah blah that is one comma here where column name operator value here where column name operator value has to specify.

[3:31:01] come down to here. So there are some examples on clause. So what is that? The very first one show all employees working in working in uh what is that uh India

[3:31:18] working in uh what is that uh India country guys. One second, guys.

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

[3:32:10] column is there in this country column we go for like this where country is equal to India has to single

[3:32:23] quotation is must because of stringle see there where class only three employees are working in India three employees are working in India

[3:32:35] that is okay okay now you tell me that show all male employees show all male employees

[3:32:47] male employees show all male employees give me the query for This show all male employees. Give me the query guys.

[3:33:18] the answer are given that is correct there

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

[3:33:46] You should use only single quotes in double quotes concept is not there in a MySQL. That means in a database systems only single quotations here only single quotations here requir

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

[3:34:29] Show all managers. This is one more you can take down. Okay. Then show all uh like employees in uh what we can call that retail

[3:34:43] in uh what we can call that retail department. Retail department that is department. Retail department that is one more. Okay. This is one thing. These are the simple conditions that is guys here. Now then show all employees. So

[3:34:57] experience is so greater than or equal to 8 whose experience is greater than or equal to 8 like this. Now see there now we have

[3:35:12] like this. Now see there now we have given conditions on only one column. All conditions. Suppose if you have the multiple column conditions then how can we go for that is let's say now here uh

[3:35:27] show all employees whose sal whose sal 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

[3:35:44] here see the query can get like this here. Now select start from EMP here. Now select start from EMP where see that guys salary greater than where see that guys salary greater than or equal to 8,000 and salary less than

[3:36:00] or equal to 10,000. You must write like this only. Okay, this 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

[3:36:17] employees are there here. Let's see their salaries here. So 9,85 9,500 10,85 is there. Fine. That is in between 8 to 10 only out of 20 employees. But guys you see there here two conditions we mentioned. Two

[3:36:35] conditions we mentioned but those two conditions on same column that range of values. In this case, so we have an operator is In this case, so we have an operator is there between is there. The same query

[3:36:50] we can write using between. How you know that? Select start from EMP How you know that? Select start from EMP where salary between

[3:37:02] 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 gives you the same answer that see there.

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

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

[3:37:47] thing that you can you can check that see 10,000 is included see 10,000 is included like that 8,000 also will be included.

[3:38:05] 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 in uh retail and automotive

[3:38:23] what? Automotive departments. from those two departments we need to get employees here. So then how can we write that you know here? So then we can go for here now where DBT

[3:38:39] So then we can go for here now where DBT is equals to retail is equals to retail R DBT is equal to automotive. You need to write like this here again one column with R operator.

[3:38:55] So that is and in the sense in same column in same here both retail and 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

[3:39:08] have healthcare like that I'm getting two you see there here select that and go for executing that is right now 11 employees are there in from those two departments 11 employees are there here

[3:39:26] trying to explain here. If you keep the under, see what happens there. under, see what happens there. No rows will come

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

[3:39:56] as automotive both values you will not find on that particular cell no and in the sense that is the meaning and or in the sense what either it is retail

[3:40:08] or else automotive any one condition is true. You got my point? Winkut.

[3:40:23] Winkut. 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 is an operator called in is there. So this kind of queries you can minimize

[3:40:40] this kind of queries you can minimize like this. What is that? You know in off like this. What is that? You know in off like this.

[3:41:01] will be getting. See retail and automotive employees are there. column because condition is on department column. Oh, there it checks department column. Oh, there it checks here

[3:41:20] is so this is the one we are have. 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.

[3:41:33] Let's see that multiple conditions on multiple columns. For example guys, you see here, show all male managers. See, if I ask like this a question, show all male managers. I want all managers

[3:41:50] all male managers. I want all managers who are whose gender is male. who are whose gender is male. Then how can we go for that is see that Then how can we go for that is see that guys select something like here where

[3:42:03] guys select something like here where gender equal to Y. So that all male people male employees will come from that one manager only will come from that one manager only managers I want that means what here

[3:42:17] managers I want that means what here role column is has to be taken and 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

[3:42:33] what that is petty Alen and Patrick Wolz is there. Now only two employees are there. Two manager male managers are there.

[3:42:45] See guys try to understand salary column between operator two conditions department column or operator that is in that means previous two

[3:42:57] examples multiple conditions on same column only we did right now multiple conditions on different columns. So now one condition on gender, one condition on male. So that is one thing we are having here.

[3:43:12] So that is one thing we are having here. Okay. So then one more query suppose here see show all female show all female employees. Show all female employees

[3:43:25] whose salary between whose salary between uh so between whose salary between uh so something like 5,000 to 10,000. Yes, guys. Will you write the answer for

[3:43:40] this? Will you write the answer for this, Will you write the answer for this, guys? I'll write the answer for this one first. Will you try for that?

[3:44:57] M gender equal to M SF and salary between 5,000 and 10,000.

[3:45:19] Yes ma'am. Gender and salary. So, answers are all answers are correct only that I'm receiving.

[3:45:39] 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 here that see all female employees see their

[3:45:56] see all female employees see their salaries 75 77 55 10,000 and 85 is there salaries 75 77 55 10,000 and 85 is there that.

[3:46:09] go for it right now. There. Yeah. Shall we go for one more? Mah Yeah. Shall we go for one more? Mah here. See show all here. See show all employees. So whose department

[3:46:23] employees. So whose department either what mother is retail either what mother is retail or automotive

[3:46:35] salaries between uh what that is 5k to 10k. Now can you make the query for this? Can you make the query for this here

[3:46:49] that values you can replace it to either any other values like 5k to 10k 10k to 15k or something else you can make it that that's not a problem so what is the answer for this ma what I'm writing there

[3:47:03] so this is the correct answer I'm writing now okay select so something like we are having where dbt in dbt in what that is retail

[3:47:24] or automotive. Okay. And salary or my salary between 5,000 to that means under 10,000

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

[3:47:55] That is one thing there. Now let's see that is here. Now show all Now let's see that is here. Now show all employees. So whose experience between employees. So whose experience between uh like what we can say that 5 to 10

[3:48:07] years. Now write the query for this. I'm seeing the data and writing that questions in front of you that to make

[3:48:21] you practice. to make you practice that is

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

[3:49:04] no so then five years show only first name only first name and uh experience column

[3:49:16] and experience columns 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 I want only those two columns I want I don't want all columns that

[3:49:42] yes in the place of star you'll get that is is right

[3:49:56] experience from EMP where experience between from EMP where experience between five and 10. We are going to write it down over there. How many employees are there? Six

[3:50:11] employees are there. See 7 years, 6 years, 8 years, 9 years. something like years, 8 years, 9 years. something like we are having that is.

[3:50:36] all senior data scientists senior data scientists what mother is senior data scientists

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

[3:51:05] first name first name, first name, uh what that is? Job and department uh what that is? Job and department columns.

[3:51:22] Yeah, department job in the sense here it is role the visibility only those three columns here

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

[3:53:07] getting any right issues while writing the queries, please the queries, please write a query here. I'll help you.

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

[3:53:39] See there I have written over there. queries here now. That means you got some exposure writing the queries and

[3:53:51] multiple conditions and all that is it 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

[3:54:04] uh one more is there an operator called like operator is there? This is everything is okay. Okay here it is not there. Huh? Okay fine that there is an operator called like

[3:54:17] operator is there. What is that like operator? How to add names of manager in the row?

[3:54:30] names of manager in the row? Next to ids right you're asking about employee ids chendra

[3:54:43] chendra so that means employee ID and uh that means you you are asking like who who is whose manager right who is whose

[3:54:58] manager that is Yeah, for that we have to write a self join self join has to be right that is joins concept I'll give the answer for this who is whose manager that one okay self

[3:55:13] join query is there now I'll show you there called what is that you know that is a like

[3:55:25] operator is there here now like operator like operator So this is used to specify So this is used to specify condition pattern based conditions

[3:55:38] condition pattern based conditions pattern based conditions pattern based conditions on string and date values date column date columns columns that we are having. This is one

[3:55:53] thing. So here two uh symbols has to be used. What is that? You know the very first one is like a percentage. So it ignores group of characters.

[3:56:12] Group of characters that then another one is underscore. Okay. Ignores single character. single character like

[3:56:25] this. What is this? You know how this can be used in the sense? For example, can be used in the sense? For example, if I ask a question like this, show all if I ask a question like this, show all employees whose first name

[3:56:40] starts with the an alphabet yes. For example, let's say an alphabet yes. For example, let's say that is that is first name first name first uh first character should be yes. Remaining all anything.

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

[3:57:08] situation sir you are having a like this. What is that? you know like select something like I'm writing that where

[3:57:20] first name like you should not use the equals to you should use like in a single quotes first letter is what yes and remaining all characters or anything

[3:57:33] so that should be ignore ignoring of remaining characters so that right now remaining characters so that right now the percentage symbol can use it here the percentage symbol can use it here percentage symbol can use it.

[3:57:47] Okay, let's see that any employees are there here with the starts with yes. Yes, only one employee is there. See there Steve. Steve is there. Let me check first of all list of

[3:58:00] employees. So you will get some couple of data over there. Yeah. of data over there. Yeah. Ah so this query you make it with K.

[3:58:12] Ah so this query you make it with K. What that is you make it with K that 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 Katrina Karen is there now see in the

[3:58:26] first name only see in the first name see in the first name so like this sir if we put here equal to what happened equal to in the sense it will search for an employee with K

[3:58:40] 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 anything that grouping

[3:58:55] remaining all 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 here what is that one first name ends with what is ends with for example let's

[3:59:10] 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. Yeah. Katrina, Diana, Cynthia just see there three employees are there. All the

[3:59:27] employees first letter is A. All the employees first last letter is A something we get that is and one more also you can go with that uh what is

[3:59:41] that? Show all employees whose first name name exist. What mother is exist alphabet? exist. What mother is exist alphabet? Exist. Exist in the sense now anywhere

[3:59:55] exists in the sense either in first position in last position in the middle anywhere if you go on that then percentage a percentage will go. Okay, that is then go for clicking of this one. Almost 13 employees are there.

[4:00:12] 20 out of 20 employees. 13 employees names are having alphabet A in their names. It might be in a different position. See Neil is there. Katran, position. See Neil is there. Katran, Karan, Williams, David, Diana, Ched, Se

[4:00:27] Clary, Patrick. See everywhere you have alphabet A. See Arthur first letter is A. Cynthia last letter is A and remaining all having some in the middle.

[4:00:42] So like this we can go this percentage symbol that depends but actually here if 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

[4:00:56] use that underscore when we use that underscore symbol the when we use that underscore symbol the underscore symbol will be used like this uh underscore in the sense what ignoring single character ignoring what that is

[4:01:12] single character ignoring what that is single character suppose same thing that let's see that is here I'm writing here now show all employees whose first names second letter starts with let's say that is second letter starts with a for

[4:01:27] example let's say that is second letter that means first letter might be anything first start letter anything that then here we can go for first you have to make one underscore then alphabet a then

[4:01:42] percentage so first letter is one underscore one letter then a then remaining all characters should ignore that you can take down here see stick the second letter of every employee here

[4:02:00] the second letter of every employee here so Janet Patrick David Karani Katrina all second letters are having a Suppose if you want third letter is a two underscores two underscores will be there. So like

[4:02:15] two underscores will be there. So like that we can take down. So then one more query you can get like this. Show all employees whose first this. Show all employees whose first name length is five characters.

[4:02:35] So if you have like this I want some employees whose name having only five letters whatever it is the letters then five underscores has to be used 1 2 3 4

[4:02:47] 5 underscores. letters. David five letters. Janet five letters. Emily P letters. Tracy P five

[4:03:02] letters. Emily P letters. Tracy P five letters

[4:03:33] 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 sir see first letter is yes sir and remaining four letters I have given ignoring yes 1ore 23 4 now

[4:03:52] have given ignoring yes 1ore 23 4 now tell me what is the result of this query

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

[4:04:30] that is the meaning okay that means so you'll get only ste this time ste only you'll get but the question goes like this here now

[4:04:46] so length should be five characters and uh name should starts with name should starts with what that is alphabet. Yes, you should go for like this. Then this

[4:05:02] you should go for like this. Then this 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

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

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

[4:05:48] just write on that here So show all employees. So whose name? Whose first name? Whose first name? Second letter Whose first name? Second letter is A. And fourth letter

[4:06:04] is A. And fourth letter is is again something E. Let's assume like that.

[4:06:17] anything. Uh [clears throat] E fourth letter is anything. Rest of letters letters ignore it. Now write the query for this.

[4:06:42] What should how to select employees first? which has a employees which has a as the last character in

[4:07:14] Chandra Praash you are missing That uh underscore for the first not percentage how you go for percentage underscore should go.

[4:07:27] Swagadika your query will check only four letters. four letters which is having second letter E and fourth letter E. You should

[4:07:40] letter E and fourth letter E. You should keep the percentage symbol after E

[4:07:57] A_E then percentage should I don't know how many employees are will come yeah we have a two K and Janet

[4:08:14] that will give us here now only four letters will be considered prai but after but the length of this name has been not defined

[4:08:26] you have to see that two lines of question rest of letters ignore that is What actual question complete

[4:08:38] 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. Now combine come to this group by

[4:08:51] clause. But one thing guys whenever you come now 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

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

[4:09:21] function should. Okay. So what are that group functions Okay. So what are that group functions and how can we use those group functions

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

[4:09:56] different operations. Different operations on the numeric columns of tables. Numeric columns only only are numeric columns that will go. Okay. Here. So the functions are like

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

[4:10:26] so that here now yeah select I'm writing sorry max of salary from EMP if you go on taking like this guys

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

[4:11:07] so let's see that is right now there what that is find max

[4:11:23] Okay. Then average sal. Average average sal. Okay. from all employees. Then how you go for that is you can go for like this guys to find the maximum

[4:11:39] 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 then total all 20 employees how much of salary they are taking there is a

[4:11:54] salary they are taking there is a function called sum added that salary so then here on an average how much of AVG salary

[4:12:08] salary from EM. That's it. Here see that the maximum salary is 16,500.

[4:12:20] 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.

[4:12:36] taking 7,815 rupees every employee. Okay. So like this we can go. So with this functions this is only we can do it here. can do it here. And one more see there here

[4:12:49] And one more see there here suppose count the 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

[4:13:04] select count of star count of star from M.

[4:13:16] So how many rows are there? That is 20 20 rows. 20 employees that every row having one employee little 20 rows 20 employees. And at the same time if you want you can count

[4:13:29] if you want you can count number of values in a column. Count number of values exist in for example

[4:13:42] 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 from emp

[4:14:03] you'll get 20 only because in a table guys listen that in a table we are not 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

[4:14:17] See there in a table complete data is filled at any moment any null values are there. Null values so will not be counted here. Null values will not be counted over there.

[4:14:31] there. That is one thing you have to remember. That is one thing you have to be remembered here. Now null values will not be counted. Let me write on the note.

[4:14:45] What mother is null values will not be counted. will not be counted. So that is one thing you have to

[4:15:01] only functions that is okay but one more point you have to remember here what is 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

[4:15:16] okay 16,500 can I get employee name of that maximum imum salary 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

[4:15:32] first underscore name and if I write this one you see there what will happen will I get will we get that name see showing an error

[4:15:48] in aggregated query without group clause cannot be used. That means guys here no cannot be used. That means guys here no column should be used directly

[4:16:00] no column should be used directly if you're not using the group by clause. So that functions what you are using max mean and all directly you have to use

[4:16:12] like this only directly you have to use like this only here this is the one we are having okay fine this is the one we are having okay fine that so now this functions is so how

[4:16:26] these functions utilize in a different ways in the sense based on these functions we have a clause is there group by clause clause. Group by clause is there. What is that group by clause? See that this clause is

[4:16:42] group by clause? See that this clause is used to divide a column into group subgroups. Subgroups based on other columns based on other

[4:16:54] columns then allows you perform group functions. what is that you know. So how the group functions so group by clause can use in

[4:17:07] functions so group by clause can use in the sense show max salaries see the here the sense show max salaries see the here now show max salaries based on each now show max salaries based on each department I write like this

[4:17:22] show select max of salary from EM in the from all the employees maximum salary is from all the employees maximum salary is showing that now I wanted to show now I wanted to show Maximum salaries on each department. Now

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

[4:17:52] from EMP. So here you have to write down group by So here you have to write down group by what is that? DB.

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

[4:18:19] column name you use in group by clause whatever the column name you use in whatever the column name you use in group by clause that column name you can mention here. Yes, like this we can do it.

[4:18:36] Yes, like this we can do it. Now see then 10,000 finance department maximum salary is 10,500 automotive is 11,000 healthcare 9,500

[4:18:53] automotive is 11,000 healthcare 9,500 all is 16,500.

[4:19:47] like this. What is that? Take this one my here.

[4:19:59] each let's make it country wise. That is a department wise. No, this is a country wise. Then can we make it like this here? Now you go for in this way country,

[4:20:13] you go for in this way country, then what should we write? M that

[4:20:31] India the maximum salary taking is right 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

[4:20:45] only one employee is there I think so so that maximum Colombia Okay. USA that is Germany and France Okay. USA that is Germany and France that Canada.

[4:21:27] so what is that you know counter number of employees in each job role each job role that is

[4:21:41] count the number of employees in the job role role okay so how that process can take here.

[4:22:05] from emp group by what that is group by ro can go group by what that is group by ro can go for it.

[4:22:25] having five members junior data scientists are having three two members like you see there now manager is five five managers are there one president one CEO something like you are having this is

[4:22:43] okay so this is the one just you can write on Okay. So this is the way we go for working on the group functions and the

[4:22:56] group by clause. Listen carefully guys. Group by clause is completely depends on the group functions individually also we can use it but most of the time group by clause can take on group functions only on group functions

[4:23:13] of chendra praash. I think so you have given one query like this. What is that given one query like this. What is that gender? Okay. Now from EM gender? Okay. Now from EM group by gender.

[4:23:32] See that two groups [snorts] the distinct clause also we did that. now. Okay. This is group by clause can use

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

[4:23:57] 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?

[4:24:09] One more is there. What here? No having clause is there. clause is there. Okay. Generally, what is the purpose of uh this one? We clause.

[4:24:21] We clause. What is the purpose of wear clause guys? purpose of wear clause? Why we are using wear clause?

[4:24:34] 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? you are we are making conditions right? We are making conditions on what columns

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

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

[4:25:21] group by only. If a group by is not there, no having clause used. But having group by clause works without having clause. But having works without having clause. But having clause cannot be used group by clause.

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

[4:25:59] So what is that one here? Let me execute this query once. extending this question like this those max

[4:26:13] sals must be must be greater than or equal to 10,000

[4:26:25] see there must be greater than or equal to 10,000 those max salaries so that means Here this 9,500 should not come

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

[4:26:51] greater than or equal to 10,000 you should write like this see I'm 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.

[4:27:06] happening on function not on the column. If it is a column we use where clause. If it is a column we use where 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

[4:27:19] without group by okay that is now you see there here see healthcare has been see there here see healthcare has been uh removed that means filtered that

[4:27:33] here the same thing just see there here uh the same thing just see there here uh what is that uh

[4:27:46] uh number of employees in each department whose counter is greater than or equal to five. Greater than or equals to 5 that is then

[4:28:01] Greater than or equals to 5 that is then how can we write that having how can we write that having count of star greater than or equal to 5 count of star greater than or equal to 5 that you can take down.

[4:28:17] senior data scientist and managers usually without that uh see there up to here in the sense what is the output you get over there see

[4:28:29] there 2 4 and 1 is there now those all has been reduced here so make it that here it is four and this is also going for four

[4:28:44] Okay. So like this you are having So like this you are having that is about the having clause. That is about what here having clause is there here.

[4:28:58] there here. Guys are you able to follow or not? following or not these queries what I'm writing group by clause having clause

[4:29:13] writing group by clause having clause group functions

[4:29:45] Yeah. So this is one setma mark. This is completely one set here. What is that? Up to where group functions group by Up to where group functions group by clause having clause up to from here

[4:29:58] that is one set. First group functions are aggregate functions then on top of are aggregate functions then on top of that group by clause then on top of that having clause. Okay, this is and if you take some other

[4:30:13] examples here you can also include the wear clauses also. You can also include the where clause is also here that is okay. So that all for

[4:30:25] that also we can do here that yeah now let's see that one query I'm asking here

[4:30:39] let me show maximum salaries maximum salaries maximum max mean salaries maximum max mean salaries what that is min salaries

[4:30:53] retail under finance department I ask like this department I ask like this retail and finance department

[4:31:07] so here you see this how I'm writing that so I'm including here group by and as well as clause select what that is dpt

[4:31:19] max of salary comma Min of salary comma Min of salary okay from EMP group by DD this is done

[4:31:38] 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 wear clause should come always after

[4:31:53] from this is the order only you have to follow. follow. You see this is this only. This is the only order we are having

[4:32:08] where group by having that is like that you will be getting that. So here see even see there if I write like this where so it shows and see where it is showing that DBT in

[4:32:23] retail comma then what that is finance showing an error that see it is not allowing you write the wear clause after

[4:32:35] the group by so take this so take this after this you

[4:33:00] Guys are you able to follow or not? understood this quiry.

[4:33:28] select statement. That is here That is here order by clause simple clause. See there order by clause. This clause

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

[4:34:02] Okay, that is so order by column name RD S is something we need to get that is S is something we need to get that is that means let's see that here

[4:34:17] order by clause. So whenever we go for like this

[4:34:29] what is that here show all employees. So in ascending order of salary ascending order of salary that is ascending order of salary that is then we can go for like this. See there

[4:34:43] select star from EMP for example see right now guys salary column see the salaries are here randomly there 7,6503 or something blah blah is there but I want first lowest salary then highest

[4:34:58] salary that then can we can write down like this order by salary now you see there how the data will come

[4:35:15] that. Okay. And finally you are having 16,500.

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

[4:35:41] clauses within it then order by clause has to yeah by default it is an ascending so that if other clauses are including order by clause has to move to the last it has to move on to the last

[4:35:57] 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, what that is finance

[4:36:13] should come. But it is not coming. No worries. Take this query here. worries. Take this query here. Okay. Right now,

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

[4:36:41] first finance and then retail will come there like we did so much of queries here. H take this query.

[4:36:54] H take this query. Take this query here. H here and apply order by clause. Order by first name D S C descending

[4:37:10] order. If you want descending order, descending order will come here. That is on first name I did that. See first it starts with Williams and

[4:37:22] See first it starts with Williams and last it goes with I. Yeah. Jed to a 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

[4:37:36] are having. So order by clause is the here the last 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

[4:37:54] like this. So

[4:38:06] show all employees in ascending order of the country then ascending order of the country then ascending order of the country then first name

[4:38:23] So you just see that select star from EMP EMP order by country first. order by country first. See there now order by country.

[4:38:37] 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?

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

[4:39:05] Diana then Emily it should come like that in that case of comma we have to that in that case of comma we have to write first name first sorting will done on country after that then first name now see this

[4:39:21] result Diana Chad Emilich Cynthia is Okay. Now see the result. See first Ched see first Canada of Canada four employees are there that has came as it is. Then you see here Ched Cynthia Diana Emily.

[4:39:37] 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 right now. Three employees are there. This is the Colombia part. Yeah.

[4:39:51] there. This is the Colombia part. Yeah. Smith. second letter. The first letter is same it takes the

[4:40:06] second letter that remember like that here. Now here. Now in that way it goes here. Now got it guys understood that is so multiple columns

[4:40:19] understood that is so multiple columns also you can take ordering like this. Guys, is it clear or not? Please let me know.

[4:40:38] Yes. Yes, Mr. Naven. Is it clear? Naven Ra. This is about today's topic here. Okay. We covered couple of commands. Still we

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

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

[4:41:20] statement is there that is called case statement statement case statement that is one

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

[4:41:51] Okay. Then the fifth one is right now there uh views. there uh views. Okay. These are the next week target. Even if the time permits we go for stored procedures, uh indexes and all

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

[4:42:15] these are the main targeted concepts in the next week. But if the time permits we go for other concepts also. So today's agenda is what we can call that

[4:42:27] 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 between tables should go with the foreign key. That is one thing. Then we

[4:42:40] 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. So first of all let's see

[4:42:52] 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 how the subqueries will work out that so that is one thing just we do right now

[4:43:08] guys uh whenever we come down this one here the subquery means a query within a query is called subquery we can call a query within a subquery

[4:43:20] 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.

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

[4:43:48] [snorts] so what is the purpose of this subqueries? So why should we write 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

[4:44:02] need to specify fixed values what that is specify specify fixed values fixed values in wear clause conditions

[4:44:17] what in where clause conditions that this is what has to 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

[4:44:32] sometimes not every time but sometimes we can avoid and we have to be avoid we we can avoid and we have to be avoid we have to be avoid specifying fixed values while writing conditions on wear clause so what are what are that one here I'll

[4:44:46] so what are what are that one here I'll show you one thing let's see so last class. So we have been discussed about uh like aggregate functions are group functions. Aggregate functions are group functions.

[4:45:01] Aggregate functions are group functions. See there now here select max of sal

[4:45:18] 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 maximum salary that employee details I want right now

[4:45:34] 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 okay that here now so select the star from EM where So what is that here now?

[4:45:51] Salary is equal to 16,500. Usually we write like this just listen my point. Usually we write like this. So is Arthur Usually we write like this. So is Arthur block CEO is the role of that person. So

[4:46:06] getting now 16,500 is the salary maximum salary. Okay. Fine. Okay. Man like today he is getting 16,500 is the salary. After few days he might get salary hike. It may go to 1 lakh 65,000 something else some hike has been done then is

[4:46:22] this query is useful on that time in the sense no that query is not useful again what you need to do here so first we have to find the maximum salary and we

[4:46:34] 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 problems we need to write two steps two select statements

[4:46:50] so can't I get this entire thing in one statement whatever it is the maximum salary doesn't matter whatever it is the maximum salary doesn't matter it has to show all the employees who is getting maximum salary

[4:47:06] all the employees who is getting maximum salary I want like that then how okay 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

[4:47:21] fixed value wherever you are mentioning in that place of by writing another in that place of by writing another query by writing another query to get the maximum salary that means can I write like this here? Now see there

[4:47:37] write like this here? Now see there select max of salary select max of salary from EM we will get like this. See there now in a simple words say same

[4:47:50] See there now in a simple words say same answer I got it right. So now I we no need to write here this query. See there we are writing one query within another query. It is now we are

[4:48:05] able to get this one. So let's see that here. Now uh what that So let's see that here. Now uh what that is show

[4:48:23] who is getting max sal something like that. In the same way see there now show an employee details who is getting min

[4:48:35] who is getting min lowest sal. So can we go for like this here? Now what is that min? Minimum is the function is there that aggregate function I'm utilizing right now that uh see there now 2,800 is the minimum sal

[4:48:51] but how just I'm showing the queries but to write this subqueries one here to write the subqueries one we have some rules.

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

[4:49:18] single column values. Single value or single column values. So based on operator used that is the first one which means so actually from this one just listen from this

[4:49:33] where is subquery in the sensema the subquery goes like this here. Now what subquery goes like this here. Now what is that? So this is what a subquery has to be written in where class of main query better you make this one into the

[4:49:50] query better you make this one into the top then that is second point So what that is has to be written in v class of main query and second one what

[4:50:03] class of main query and second one what is that subquery must return single value or single column values based on operator used. Then here first see that operator used. Then here first see that first subquery will be executed

[4:50:17] based on the subquery result main query get executes 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

[4:50:31] which means when it coming to right now there you see this is the subquery. This is the subquery here. This is called subquery and this is called main query. This is called what here? Main

[4:50:44] query. Okay. First subquery will get executed. Whatever the subquery you write that query will get executed. You will get an answer. So suppose see I selected this part now then go for executing of this see 16,500 is there

[4:51:02] okay that is one thing then that now select start from emp where salary equal select start from emp where salary equal to 16,500 main query will got executed that and here also same thing guys first subquery will get executed 2800 will

[4:51:17] come here now see that 2,800 then main query goes select start from emp salary equal to 2,000 and 800. The minimum salary employee details will come that salary employee details will come that is okay. And that to one thing this

[4:51:31] subquery must be written single value one value 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

[4:51:48] here. Now you are using here equal to or else less than equal to or else greater tell me that guys if you are using greater than less than equals not equal in the sense how many values it will take.

[4:52:04] How many values it will take guys take? Only one value. Yes, that is okay. one

[4:52:16] Only one value. Yes, that is okay. one value that one suppose in case if I write in operator then how many values write in operator then how many values it will take

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

[4:52:42] values commas operated that to on multiple values of multiple columns are single column multiple values of multiple columns are multiple values of multiple columns are single column that you tell me guys

[4:52:56] guess it single column only that point you have single column only that point you have to remember okay see where salary in for example [clears throat] like see salary in suppose if I'm writing like this

[4:53:12] 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 values from the salary column only so multiple values of single column that is

[4:53:27] the point here we are I'm mentioning here now see there you can what that is a subquery must be written single value or single column values based on the operator used

[4:53:43] the operator what you are using here. Okay. Equal to not equal, less than or 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

[4:53:58] based on that only here you have to write the query. 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

[4:54:11] it. That point you keep in mind. Okay, that is my now see there with the Okay, that is my now see there with the sub queries we got it here this way. So this is why let's have another example of subquery

[4:54:26] example of subquery can I get like see see this is what here now showing maximum salary it is what that showing maximum

[4:54:38] salary it is what that showing maximum salary it is suppose if I ask okay show salary it is suppose if I ask okay show second maximum salary

[4:54:51] second maximum salary employee details I want only second maximum salary I want it here then how can we go for that second then how can we go for that second maximum salary here

[4:55:04] yeah with the help of subquery I'm solving this see there select max salary solving this see there select max salary from emp it's a very little tricky here salary less than

[4:55:20] within this again going for select max of salary from EMP that I'm right from EMP that I'm right understood this one properly

[4:55:38] 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 ma what is the operator we used

[4:55:54] query ma what is the operator we used here what less than first this is what 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

[4:56:08] written 16,500 now you understood select max salary now you understood select max salary from EM where salary less than 16,500 from EM where salary less than 16,500 which means that it is finding maximum

[4:56:23] salary from all salaries of salary column by excluding by excluding 16,500 column by excluding by excluding 16,500 because you used here less than operator

[4:56:37] 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 Ma

[4:56:52] clear or not that query please let me know know are you able to follow or not again if you put now less than or equals to again 60,500 will come it it goes

[4:57:07] to again 60,500 will come it it goes with the 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 that you have to go for only less than

[4:57:21] that you have to go for only less than that is maximum salary exclude. Now we that is maximum salary exclude. Now we are getting this is

[4:57:34] yeah like this we get and one more that you see this here. Yeah. So show an employee details

[4:57:54] second maximum sal now you see there now so for this I'm going to write a subquery within a subquery you can write

[4:58:06] like that a subquery within a subquery you can write on you can go with subquery within a subquery you can write like this. It is possible one subquery within another subquery you can write answer

[4:58:24] subquery for this see ward I'll show you now just wait first [snorts] let me finish this that's what I'm telling right now ward the same the situation will come here show second sorry employee details who

[4:58:41] show second sorry employee details who is getting second max okay so then can Can we write like this? Ma select star from EMP where from EMP where you'll get it down here. Now salary

[4:58:55] is equals to okay can we get this? This is giving okay can we get this? This is giving second maximum salary that query that I'm adding here that I'm adding. See there one subquery

[4:59:12] within another subquery an employee details who is getting an employee details who is getting second maximum salary 14,500

[4:59:25] that subquery I'm writing one more subquery here so how this will get executed how this will get executed ma first this how this will get executed ma first this will get executed okay 16,500 will

[4:59:41] Then continuation to this will get executed 14,500 will come then this will get executed that employee details will come reverse mechanism in a reverse

[4:59:53] order you have to go for that in a reverse order you have to go for that here guys understood or not this query

[5:00:35] 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 your calculation is correct whatever you are thinking that's correct here

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

[5:01:06] And that is possible. Okay. But we are not using any group by clause here. So that's what

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

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

[5:01:46] where salary again going for less than what that max of salary permp what that max of salary permp we are taking like Obviously

[5:02:00] you get third maximum salaries 11,000 is there

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

[5:02:31] max and mean salaries. How we write a query for this? How we write a query for this ma? I want an employee details who is in one query I want maximum salary employee and

[5:02:47] minimum salary employees both two employees I want right now employees I want right now two employees we can write on over them shall we go for like this select

[5:03:01] select star from can anyone try that please try I want an employee details So he's getting match and min

[5:04:21] I'm giving one clue also. You have to use in operator.

[5:05:01] anyone ma should I Hey,

[5:05:30] we get. See current has given current Jani has given answer that see there select start from EMP where salary in

[5:05:45] just I'm taking like this here now you write write select okay one thing we do here

[5:05:59] okay from EM that is one query. Put a comma. Then you go for that here. Select max of salary. Max of salary. Yeah.

[5:06:16] From EM. Okay. Then this is done. See that this one. This is one method. So first two

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

[5:06:43] See there now you get those two employee details.

[5:06:55] 16,500. The two employee details we got it there.

[5:07:18] Fine. So, why we are using brackets for getting values of quaries? The brackets part it's not I think so optional guys here. Uh it's No, see actually here it won't allow you

[5:07:33] here 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 that parenthesis. Okay, now it works there.

[5:07:53] Got it? Navindra Naven Raj sorry

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

[5:08:30] all employees who are having who are who have who have above the max salary

[5:08:43] above the max salary above the max salaries of what we can call retail department. Hm I want like this now Hm I want like this now retail department

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

[5:09:23] maximum salary from retail department and show all the employees who is getting above that maximum salary of retail department.

[5:09:38] How you go about that? Shall we write like this? Select. So first of all you find the maximum salary. So select max of salary

[5:09:53] So select max of salary from EM see there now here where what from EM 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?

[5:10:07] That will give you the see that 10,000 we got it that. So then simply you go for that here. Select start from EM where okay salary

[5:10:22] Select start from EM where okay salary in not in sorry greater than this is above no I asked greater than this is above no I asked about that so that we go for here now

[5:10:36] about that so that we go for here now see there here there now which is from different departments.

[5:11:02] that first you write a subquery based on that first you write a subquery based on that you go for there. another way. Of course sir the group by clause we no

[5:11:18] 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? Uh see there this clause you can make it

[5:11:34] like this. So this one [clears throat] So this one [clears throat] usually we write like this. No usually we write like this. No group by what that is DB I'm writing. So

[5:11:47] all department maximum salaries will come come from that uh you get it only uh like what retail department group by uh maximum salary

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

[5:12:12] that is also fine. This also you can add it here. This is also works here. Now or both will give you the same answer.

[5:12:30] See there. In any way you can work 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.

[5:12:48] querying of these what we can call subqueries. We'll do some other subqueries with assignments. We'll do that not a problem. that not a problem. Let's move on to another topic called in

[5:13:01] our uh today's agenda table creation with the foreign key. Table creation with the foreign key. Guys before going that please let me know anybody having any queries or else is everyone understood writing the

[5:13:17] is everyone understood writing the subqueries guys? everyone understood. If anyone having any query, please post

[5:13:31] If anyone having any query, please post it here so that we'll discuss

[5:13:44] please guys. So I'm moving on to next topic right

[5:14:09] so this is about uh let me save this one like a sub queries 21st February like a sub queries 21st February 20 first web subqueries.

[5:14:22] Yes, this is the one. I'm just taking that. that. Yes. So going to another one. Yeah. [snorts] So now the next point here now let's see

[5:14:36] that the next topic for today's one that so creating relationship between tables using primary key and foreign key that is what we are to go how primary key and

[5:14:49] foreign key part will work here that is that means guys here the relationship has to make between the tables first of all why this relationship should So, so

[5:15:02] for that I'll show you one thing that here

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

[5:15:36] Okay. Ro number, student name like we go for gender. So now here city something we have something like this here. something like this here. So now we are having that this is

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

[5:16:04] we have one that is three students are there here so now I'm taking that there here so now I'm taking that marks is one table you are having in this marks table. So what I'm taking you know serial number exam subject one

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

[5:16:34] something like uh so what do you say that here so quarterly is an exam that here so quarterly is an exam okay some 56 78 some we got some total here now let's go for that

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

[5:17:07] for 60 again 60 120 it is there 1,1 let's see that is so 3 again quarterly 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

[5:17:20] 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 14

[5:17:33] that if I add 14 it should not accept here because here one thing that guys here the marks what we are entering here the marks okay That marks are completely it depends on the the students who are who

[5:17:50] are there in the by data table. 10,001 role number 10,002 10,003 is there now role number 10,002 10,003 is there now that we are taking this is okay this is also okay this is also okay because these role numbers are there here but uh

[5:18:06] it is not acceptable if I want the role number who are having role number 104 by data details can we get from this table

[5:18:18] 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 excel so that we are able to write but

[5:18:34] it should not happen on data basis so that we have to make a relationship so that we have to make a relationship between these two columns.

[5:18:48] these two columns here that is okay. So whenever we make relationship between these two columns now here it is a primary key. Here it is what primary

[5:19:00] a primary key. Here it is what primary key it is simple wayma that is it is a primary key and here it is what in the sensema here and here it is what in the sensema here foreign key

[5:19:19] primary key and foreign key will go per line. So in foreign key column whatever the values you are entering this column will refer always the value existency in

[5:19:31] primary key of by data table in this role number column that means if I'm entering here 102 first it has to check that 102 is there or not here if it is that 102 is there or not here if it is there it will accept it has to accept

[5:19:45] okay if it is not there it won't accept here here so data consistent Prospection should go so data consistent Prospection should go see employees suppose a department table

[5:19:59] employee table. So employees are registered in a department based on the registered in a department based on the department only. That is one thing. Okay. Like like you can s you can go with like country names and their

[5:20:13] capital cities. See there. Now first I'll write all the countries then I'm going to add the capital cities. capital cities. So based on the country reference

[5:20:27] so the in that way just it goes there the relations here now. So there are a different ways that here now see I'll show you now there wrong you delete this one here just see that

[5:20:42] four ways what that is the relationship you can what that is the relationship you can take it in now four ways that here first relationship is one to one is there now

[5:20:55] whatma that is one to one relationship will be there. So one one to one in the sense there. So one entity here entity A is there then

[5:21:10] one entity here entity A is there then entity B is there. Okay in between that we will make a relationship. This is called here now one to one relationship. What is that one to2 one to1

[5:21:23] relationship here? See there now one to one relationship.

[5:21:35] Okay. In this one to one relationship. 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 course. Can we make it like this?

[5:21:50] One student one course. one student one course one to one one student one course one to one relationship can take that is here. So that is one we can take down there. So one more is there here that one to many.

[5:22:06] 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 what that is. So this is one just we can take down here.

[5:22:23] take 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.

[5:22:36] [clears throat] 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

[5:22:54] report consist what that is consist of customer and product details product details here here this you can make sales

[5:23:11] And this is going for what here let's consider. So that customer and it this you can take what mother is product something you can and you can make reverse also many to one also going for same thing

[5:23:25] many to one also going for same thing that what mother is many to one also you can make that is that is also one more point you will get that is also one more point you will get there

[5:23:41] So there are three has been covered. Three has been covered here. That is Three has been covered here. That is next. Fourth one here that is many to many. Many to many that we can take down that

[5:23:56] Many to many that we can take down that is. So this is one more just you can take down here.

[5:24:11] So how you can go for that is right now there so A and you make it see that and 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

[5:24:27] Okay that is one thing just uh we are getting that. So A to C you can get A to 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

[5:24:43] here now many to many what that is many to many you can take that is here 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 as

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

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

[5:25:43] multiple products products okay that you can take down here.

[5:25:55] 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 it goes. So primary key

[5:26:11] and foreign key concept. Let's go for working on that 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

[5:26:28] will 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 see that. So here role number column is there. Here also role number column is

[5:26:43] there. Here also role number column is there. That is one thing. Second point in parent table common column should have a primary key. So that is one thing. Suppose if you consider this is the primary parent

[5:26:57] table. Okay. So then this should be a primary in a child table. Common column will have foreign key. Obviously that common column in child table becomes here now foreign key.

[5:27:12] See here in primary key here you have unique values but here you might have the duplicates how it is in this table only just check that so three students only just check that so three students has been written quarterly exams suppose

[5:27:26] if half early exams has been written that okay again that role number will be repeated no role number will be repeated because of role number will be repeated because of that it's a half exams that it is what

[5:27:40] that it's a half exams that it is what here now half exams that we go for here now half exams that we go for there.

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

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

[5:28:22] foreign key how to write down that column name references parent table of column name references parent table of name column name has to go let's see now that guys this is the example We'll make it that here now one

[5:28:37] 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 primary key

[5:28:52] then here see transaction serial number is a primary key account number transaction type transaction date account number is a foreign key let's see that how can we make these two table creations here.

[5:29:07] 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.

[5:29:26] Yes. So then here first I'm creating a parent So then here first I'm creating a parent table. So that create a table customer so that you write that is a serial number it is into unique

[5:29:40] okay int unique I'm taking that then came name I'm making that is where of some 20 then location I'm making that so where carare of 10 so make it a default value okay what

[5:29:57] that is hyd Okay. So then now I'm just taking what that is. So account number what that is account number worker of some 10. So primary key

[5:30:21] duplicates won't allow but a null value is possible. One null value is possible. Primary key in this sense no duplicates no nulls.

[5:30:33] That is the difference. Very good question that everyone thinks that primary key in the sense only unique not that one. Primary key is a combination of unique and not null.

[5:30:46] unique in the sense now there unique in the sense duplicates won't be allowed the sense duplicates won't be allowed but one null value can allow one null value can allow you see that guys I'll show you here for example

[5:31:02] okay here only you just take that it is not a don't consider like a relationship here only this one I got here now unique constraint one the unique constraint okay here I don't know one of the student role

[5:31:17] don't know one of the student role number fine no worries I'm writing name and I'm giving that and again I'm going for that yeah that's what that is I'm explaining navindra navin ra just see there let me

[5:31:32] navindra navin ra just see there let me finish this okay student uh role number I don't know for example if I'm giving like this here

[5:31:47] 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 constraint violated again unique constraint violated here

[5:32:02] so that is one point you have to remember it won't allow here no there is a chance of only one null value another null value in the sense again duplication is coming again duplication is coming here so it won't allow if it

[5:32:18] is a primary key even that null value also will not be allowed completely also will not be allowed completely unique and not null I hope you got it unique and not null I hope you got it that understood

[5:32:37] yeah that makes the difference here now yeah now I'm creating parent table here yes I created a parent table. Now let's go for that child table that so here

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

[5:33:06] sense you can you no need to enter the values into that table we I already discussed that starts from 1 2 3 4 it will go that I'm giving will go that I'm giving Okay. Then T type you just write on T

[5:33:21] Okay. Then T type you just write on T type. T type is workare of some 10. Then T date transaction date. It's a date column. You just make that easier. Now column. You just make that easier. Now then amount what is the amount has been?

[5:33:36] So one second that here account number you write on it's a vare of 10. I'm right I have written that then 10. I'm right I have written that then here what is another one amount just to

[5:33:52] go for that it is int yes so like this we mentioned now for this column we we mentioned now for this column we wanted to make primary sorry foreign key for this column we wanted to make what that is foreign key here now here you

[5:34:07] write down yes foreign key of account number. Okay. References.

[5:34:19] References. Where is the parent table? Customer. Customer of and customer. What is the column? It has to go for refer account number only. Yes. Like this. You have to write applying the foreign key like this.

[5:34:34] 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. Primary key foreign key part has been taken right now.

[5:34:48] 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 you go on that that is okay it will shows you like this. Yeah.

[5:35:04] So that is one thing we are having then uh so describe trans is the table I just created [clears throat] right now there see here you can see that is so if it is

[5:35:18] having mul that means it is having foreign key constraint that is the foreign key constraint that is the meaning you have to remember m in the sense of here whatma that is foreign key

[5:35:32] say that from which column to which column you have a primary key and primary key and foreign key that is there there guys.

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

[5:36:19] Insertion of data has to be done first parent table then what my child table. Okay. Add some rows into the parent table. Add some rows into parent table. guys that here

[5:36:33] that here insert into what that is customer so you insert into what that is customer so you just go for serial number C name then account number location is a default one so that I'm

[5:36:46] 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 001 I'm writing doing that.

[5:37:06] Let's have three accounts. Yes.

[5:37:22] Now just three rows are inserted. See there just three rows are inserted. See there right now there row

[5:37:37] Now here here we have to check the foreign key. How we have to check the foreign key here? Let's go for that insert into insert into serial uh okay transfer. Okay. So open

[5:37:50] 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 number

[5:38:04] to which account it is so that here you just see there acc 02 let's go for that just see there acc 02 let's go for that one here then transaction type debit

[5:38:16] then transaction date let's say that here now first year 2026 - 02 - 10th date let's go for that it is a 10,000 amount okay

[5:38:30] I got it like this so this row will be inserted now see the row has been inserted because account number two is there so let's check that uh sorry let's check that the account number

[5:38:48] which is not present on the parent table while inserting the rows. Okay, 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.

[5:39:04] it like all the transactions are same. Just go for this. Let's see this is see you got an error here. See there you got an error over there. See there you got an error over there. Here now it is not inserted. What is

[5:39:17] that error statement? See there here error code 1452 cannot add or update a child row a foreign key constraint fails

[5:39:35] foreign key whatma 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 the buy data table so it is not accepted That is what my here we are avoiding

[5:39:50] inconsistence data. We are avoiding inconsistency data. For example, if I go for that 10,0009, it should not accept because 109 is not presented here. Okay. So this is the one just we have to

[5:40:05] 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 is accepted. See that is now you go for account number 10,2

[5:40:19] account number 10,2 let's go for that citor and here now some 14th date okay the something yes accepted and again you just go for that what do

[5:40:34] you say that is this is something like a debit debit what that is debit some rupees just have like this some transaction ctions some random transactions I'm adding that

[5:40:48] random transactions I'm adding that now you go for here see there now got it the something like we are having got it the something like we are having this is

[5:41:04] understood or not guys please let me know if the primary key foreign key creation relationship tables What is your query here? Uh if I want to

[5:41:18] create uh yeah sjit just wait I'll post it. Yeah. Now come down to so here account number is primary key. So it won't allow

[5:41:31] 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. I have given all the list of columns in transaction table that finally what I

[5:41:46] started here now see and now to this column I'm wanted to give foreign key there something some relationship which refers to the customer table of account number column so what have I done foreign key of account number what is

[5:42:02] this account number this account number is the column name of this is this 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

[5:42:16] table it's a parent table now customer table on the customer table to which column it should refer account number column column like that it goes

[5:42:37] 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? Huh?

[5:42:49] right now in this relationships. So, two points has been done. First of So, two points has been done. First of all, uh

[5:43:01] is only one multiple columns should not get the primary key. Remember that one point unique you can give any to any number of columns. Primary key is only one constraint for that one to the multiple

[5:43:16] constraint for that one to the multiple columns. We cannot give primary key. this one. So this is the one we go for a deletion. Suppose right now for updation

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

[5:43:43] that means ma for example just come down here guys try to follow

[5:43:56] 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 detail I wanted to delete pawani details here pani details I wanted to delete

[5:44:11] let's see that whether the delete command works or not here command works or not here delete from customer the simma here delete from customer the simma here where account number is equals to here

[5:44:25] acc00002 I'm writing let us see that here now whether it deletes or or not. Let us see whether it is deletes or not here. Now whether it is deletes or not here. Now see it is not deleting that row.

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

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

[5:45:10] delete from customer where account number equal to 00 to see error code 14 number equal to 00 to see error code 14 what that is so 1451 cannot delete or what that is so 1451 cannot delete or update a parent row.

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

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

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

[5:46:20] first delete all the rows from the child table first delete all rows from child table then go on to delete the parent table like that it should go now see there here now what I'm doing here

[5:46:37] the same delete command first you delete rows from the transactions okay trans you go

[5:46:50] yes see there from the child table we deleted rows has been deleted two rows deleted rows has been deleted two rows has been deleted You check that here.

[5:47:03] here. Now you delete the rows from the customer table. See now it has been deleted. Customer table delete command has been worked here. Now you see this

[5:47:24] transactions for account number three in a transaction table? Is there any transactions in account number three in transaction table? Ma,

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

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

[5:48:07] sir how it is deleted without deleting rows from the transaction since the transactions for the account number three. So it will delete if any three. So it will delete if any dependency is there it won't

[5:48:21] in that case first you have to go to the child table on the child table you have to delete all the rows then come back to the parent table then delete that the parent table then delete that particular uh account details but it is

[5:48:33] particular uh account details but it is a little bit cumbersome task or not guys tell me little bit cumbersome task or not it Obviously

[5:48:48] something but when you go to the live databases you have n number of rows are 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

[5:49:03] searching the data deleting in the sense combustion task. So database will give us one option. What is that in the sens? If you apply a delete command on parent table, if you apply a delete command on

[5:49:20] table, if you apply a delete 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 is like that, it will be easy for us.

[5:49:37] 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 fine. Anyhow, you have decided one row from the parent table. So there is no

[5:49:49] 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 parent table and as well as delete that relevant rows into the child table also.

[5:50:01] Then how can we do that? In the sense while creating parent sorry while creating child tables while applying foreign key there is a an option called there are two options is there on delete cascade or on update cascade.

[5:50:20] cascade or on update cascade. On delete cascade or on update cascade is there. What are that one? See ondee cascade. on delete casket. When we apply this option to a child table,

[5:50:35] option to a child table, when we apply this option on a child when we apply this option on a child table, it allows to delete rows from parent and child tables at a time. When we apply delete command on parent table

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

[5:51:09] apply update command on parent table that is what? So shall we apply these two options right now by creating another pair of parent and child tables? another pair of parent and child tables? Shall I split that m?

[5:51:24] tables that so just have some few columns less number of column that would be better. Now here I'm creating a table

[5:51:36] dpd department that is department number in department that is department number in primary key it's a parent table primary key it's a parent table department name okay where caro sum 20

[5:51:51] okay that's enough two columns is enough because concept is important that's find because concept is important that's find that here are two columns that here are two columns so insert into db PD okay department

[5:52:04] so insert into db PD okay department number department name okay values 1 number department name okay values 1 sorry 10 now so then it is sales department so then here 20

[5:52:19] so then here 20 here now education department here now education department so then one more here uh 30 so here like what do you say that is research department like the three departments I

[5:52:33] added now let's have that if yeah fine let's have that if yeah fine now I'm creating here create

[5:52:48] table empo okay here now emp number it is into 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

[5:53:01] leave it that Okay, department number column I'm taking as int. Just listen my point. Department number column as int that is has been taken any other columns we don't want that as of now. Now see that I'm applying foreign key

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

[5:53:30] number after writing this here you write on on delete cascade space on update you have to write like this then close it

[5:53:42] it you have to Write like this here now. you have to Write like this here now. Okay. Then go for executing that. Now insert the employee data here. Insert employee data. See there insert into emp

[5:53:58] info empa eame department number. eame department number. Okay. So then go for values 1001.

[5:54:10] So Ramana. Okay. Here now. So department number 20. Okay. Here now. So department number 20. Yes. You add some of the employees.

[5:54:22] Yes. You add some of the employees. You add five employees. That You add five employees. That like we have some employees here.

[5:54:34] this one my here EMP info. I'm just taking that. Yeah, got it. So now you apply a delete

[5:54:47] Yeah, got it. So now you apply a delete command on a DD table. So to check whether the ondee casket is working or not, what I'm writing delete working or not, what I'm writing delete from DD where department number is

[5:55:00] from DD where department number is equals to I'm deleting department 20. Just see there now see guys listen carefully. I'm not applying delete command on emp info table. It's a child table. I'm [clears throat] applying

[5:55:15] delete command on d table which is a 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

[5:55:30] Okay. Now you check the data in both tables Yes, department 20D is not there. And now you check

[5:55:43] And now you check data in EMP table employee info table. Okay. See there now department 20 employees are deleted automatically but EMP info on delete cascade.

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

[5:56:20] 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. No,

[5:56:40] from that data sets guys you see there here SQL data sets go to assisted here SQL data sets go to assisted practice data sets on the assisted proxics data sets lesson number five is there sorry

[5:56:55] there sorry not that le data sets is there in this leap data sets you have very lesson

[5:57:09] for retail M management data set is there. There you have some three CSV files. I hope that everyone got it on your side. Right. Please check on your

[5:57:21] side guys and please confirm that you are having or not these three Excel are having or not these three Excel sheets.

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

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

[5:58:10] lesson five retail M management data set is there there you have got uh 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

[5:58:25] if anyone is not getting I'll share it on into the chat box if required up. on into the chat box if required 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

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

[5:58:52] These are the three tables having primary key, foreign key, relationship, data ids. See there first of all you go to customers table guys. Customer data set or customers table. If you observe this customer table, you see there how

[5:59:08] what are the columns you have? Customer ID, customer name, customer location and customer phone number is there. This is one parent tables.

[5:59:21] Okay. So then products data set is there products. See there now here you have got product ID. You can check on your side also. This is the product ID is there. Totally we have some 50 products

[5:59:39] or something is there. 26 products are there. Fine. Then the product name is there. Okay. A price is there. Then stock is there. Then category is there. This is different categories. So yes, this is also one of the parent table.

[5:59:56] this is also one of the parent table. Now here we have one table called sales. We have one table called sales is there in the sales table guys. Here now see there now ordered date

[6:00:12] guys. You see there that this is the sales report. It is a child table of both customers and products. Ordered date, ordered number, see their customer ID. This is the foreign key column. This is

[6:00:25] This is the foreign key column. This is the foreign key column. That then one 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.

[6:00:37] Why? Because in the sense even if we keep nothing will happen. Okay. But customer 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

[6:00:52] product code is there. Product name is there. Quantity amount is there. Guys 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.

[6:01:09] If the column names are having same names product code, product name, I think it is. is. Yeah, if it is same, no worries. Got it.

[6:01:23] Then product code is there. It is one of the foreign key from the product table. Product name. This column also we don't required. required. So totally we have a 26 products. So 11

[6:01:35] customers are there. I think 11 customers. But here how many in a sales customers. 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 so

[6:01:52] 11 rows are there 11 in the sense 10 products has been sold out of 26 products has been sold out of 26 products yeah find that okay now let's products yeah find that okay now let's start importing of these things my here

[6:02:07] so now go to your database. Right click on the tables. Right click on the tables. Go to the table data import wizard. So go to the browse. Do it on your side map. This is my side. Do it on your

[6:02:21] side. Okay.

[6:02:35] is first you take the customer's part. 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 tables

[6:02:47] partma you keep table name as a customers what that is customers you just keep it customers then go to the next then next

[6:03:01] and next and go to the finish part until you make it finish you make it finish This one table is there here. Yes, customers is the table. We have a customer that's leave it method.

[6:03:16] customer that'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.

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

[6:03:43] make it products and go to the sales. Okay, here that is So then go for sales guys. Here sales data set is there

[6:03:59] in a sales data set. Make it sales that make it verma sales guys here guys a small modification do

[6:04:12] guys here guys a small modification do it here. Now in this situ situation okay in this situation uncheck this C name column P name column. Uncheck those two columns. Uncheck that C name and P name column and go to the next and next

[6:04:29] and make it finish. Yeah guys now my point is right now here let's see joins concept is joins so now the joins concept completely we will work it on these three tables

[6:04:45] 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 a joins in the sense maj see there the process of retrieving data from multiple

[6:05:00] process of retrieving data from multiple table When those tables are in when those tables tables are in relationship that is

[6:05:12] see we can retrieve data from multiple tables but uh not taking from random tables that take one student table one employee table and I retrieve the data from those two tables it's not possible like that

[6:05:27] okay so when the table and tables are in relationship key that means primary key, foreign key from that tables you want some valid data. You want to retrieve that in the sense so then joins process can use it.

[6:05:41] As you know that when you are having tables and relationship as you know that when you are having relationship tables that so definitely you will be having a common column between a parent and child table.

[6:05:54] Parent and child table that okay that is one thing you have to remember. So whenever you observe here it the tables which we have taken there. Okay. So here

[6:06:06] 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 table product ID product code is there product code is there. So then uh in a

[6:06:19] sales table in a sales table must see 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

[6:06:35] like that so when the tables are under joining sorry when the tables are under relationship by using join process you can retrieve the data you can retrieve the data Yeah, how we can go for retrieving data

[6:06:50] from multiple tables using joins. This is the syntax you have to follow. This is the syntax we have to follow that. See select what that is? See since we

[6:07:02] are getting data from multiple tables, since we are getting 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

[6:07:17] dot column 3 like this again another table called table 2 column 1 table two dot column 2 like this you have to mention then afterwards from table one

[6:07:30] mention then afterwards from table one join type table two join type table two table one join type table two and uh on join condition this is the process you want to take

[6:07:42] join Join condition in the sense what he get here what is a join condition here let's see join condition is a condition join condition is a condition has to be

[6:07:54] placed common column just see common column of joining tables so that is one thing just we are taking here yeah common column that is common

[6:08:06] column of joining tables what how the condition should go on table one do common column equal to table two dot common column.

[6:08:19] example, customer dot customer id equal to sales dot customer id or else products.p product code equals to sales.product code. It comes like this.

[6:08:31] While writing queries you will come to know. While writing queries you will come to know here. Then what is a join type? Here we have a joint types are there here. Now what are this joint type in the sense my here there are different

[6:08:46] in the sense my here there are different type of joints as follows one is my here inner join is there second one outer join that again classified into two types that left outer join right outer joint then we have a cross join then we

[6:09:00] 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 join. So what is the inner join here?

[6:09:17] Whenever you go for the inner join mi see the process of the process of retrieving rows from joining tables for only matching values are rows exa exist in common column between parent and child tables that is joining tables.

[6:09:36] Joining tables that matching rows should go here. matching rows. So that's what we do here. Now let's see that how it comes right now

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

[6:10:01] 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 tables sales is a child table

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

[6:10:30] join you can write first we write one join quer two tables okay so now so join quer two tables okay so now so anyhow you are seeing here now see there

[6:10:42] what is this customer id is there now I'm writing a join query join see there see there between customers and sales.

[6:10:57] between customers and sales. Okay, this is so now first you go to Okay, this is so now first you go to right now select store from sorry

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

[6:11:23] write on select customers dot star comma products dot star sorry not products products dot star sorry not products very sorry guys sales dotstar okay that is from see there here customers

[6:11:44] customers inner joy in sales then on what that is customers do C ID then on what that is customers do C ID is equal to sales dot C id

[6:11:57] is equal to sales dot C id you should get like this here customer's table when you forget that select star from here here What is the

[6:12:11] star represents here? All columns. Now I'm bringing all columns from customers table. All columns from sales table. That is

[6:12:29] customers are there here now? 13 customers are there. We have how many customers? 13 customers are there. Okay. Now this is what we are having. Yeah. Now this is what we are having. Yeah. Fine. And uh you go for the sales table.

[6:12:44] 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 here and 10 customers has been purchased.

[6:12:58] So what is the output you get in the 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 join condition only the rows will be

[6:13:14] join condition only the rows will be retrieved okay we do one thing just see there here and copy the values of this column yeah I'm copying this so how it works I'll tell you ma

[6:13:30] how it works I'll tell you yes I added these all this is for what customers What is this customers here? Now go to the sales how the inner join

[6:13:43] works. That is I'm trying to explain you take this column. take this column. Okay. And I'm copying that. And here you just paste it. That is Yeah. And this is for what? Here now

[6:13:59] 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 here it doesn't matter doesn't matter that how it works there only matching

[6:14:12] rows will come only matching rows here customer ID is there what is that four once this will be compared into the sales table of customer ID four once is sales table of customer ID four once is there yes we have here so that

[6:14:26] there yes we have here so that particular details will come then 12 to 1 you check 1 12:1 is there is or not? Yes, it is also there. Yes, it will Yes, it is also there. Yes, it will come. Then 126. Is there any 1 126? I

[6:14:40] come. Then 126. Is there any 1 126? I think so. No. Then 12 46. Yes. 1 2 4 6 is there. Fine. Then 133 is there. Is there any 1 Fine. Then 133 is there. Is there any 1 1313? No. 1 9 1 0 is there. Yes. 1 9 1 0

[6:14:55] is there. Then 2 1 2 3 is there. 2 1 2 3. Yes, it is there here. Then 3452 is there. Is there any

[6:15:07] here. Then 3452 is there. Is there any 3452? No. Then 3 921 is there. Yeah. 3 921 it is there. Here. See there. Here you have. you have. Then 5334 is there.

[6:15:21] Then 5334 is there. 5334 is there. Yes, it is also there. 5334 is there. Yes, it is also there. Yes, that is. Then 9021. Is there any Yes, that is. Then 9021. Is there any 9021? I think so. No. 9212. Yes, it is

[6:15:33] there. First one. So then 9875 is there. Yes. Here we are having 9875 is there.

[6:15:49] missing, right? Huh? Yeah. Yeah, that means one product has been

[6:16:03] one customer has been done two times. That means two products might be purchased. Yes, correct. So now that is so what are the others that so here you see this one here. So now whenever you go for right now

[6:16:19] so this this product this customer details won't come. This is okay. This customer details won't come because these customers are there but they haven't purchased anything. That is the simple panda. That's it.

[6:16:37] Okay. These these are skipped remaining all because matching values are there that will come there. Okay. That is the one thing just you have to remember. Let's see now here. So go to. So this one

[6:16:52] go to. So this one here now executed this whatma execute this process. Yes. See only we have 10 rows.

[6:17:04] Yes. See only we have 10 rows. 10 rows we are having. That is just wait

[6:17:54] customer ID, customer name, location, phone number. Then see their ordered date, order number, see their customer ID and what that is product code which has been purchased this and this is

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

[6:18:25] this what is the question goes in the sense here sense here show see there show all customers who

[6:18:37] are purchased What that is who are purchased products which products purchased we don't which products purchased we don't require here who purchased the products

[6:18:51] who purchased the products that that's it I want that information so this query I'm just taking into like this what is that here

[6:19:04] that here okay customers dot you just go for customer name that means C name that is one thing that we are having

[6:19:16] that is one thing that we are having okay so customers dot then what that is location that's enough I want only those two columns that from the sales table what I want in the sense here I want order date

[6:19:33] want in the sense here I want order date then sales dot order number okay that Then sales dot quantity what method is quantity then sales dot

[6:19:50] amount so like this you have to go what are the products they purchased we don't require that don't require that any column is missing C name actually

[6:20:04] any column is missing C name actually it's not C C name it's C I mean it's not C C name it's C I mean see there guys here see this is these customers has been done some purchasing

[6:20:22] purchasing that is see the who is that here Niha Kerala Niha from Kisha from Kerala has been purchased has been ordered some quantity and amount

[6:20:35] like this you are Having this is what guys is it understood guys this is what guys is it understood guys is this query

[6:20:53] join compares here please let me know guys I have been explained in Excel how the inner join works here by copy pasting of these things there.

[6:21:11] uh one more what is that you know here

[6:21:25] 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 if

[6:21:43] So then how can we go for that if so for this here the products is there right now okay so now what I'm taking that select

[6:21:58] products dotp name okay then now products dot price dot price Then products dot stock.

[6:22:13] Okay, that is then products dot that category we don't require. That's category we don't require. That's enough. Then go to the sales table. Sales table is what you just take only these two.

[6:22:27] 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 products are there. You see this 26 products are there but 26 products is

[6:22:44] not sold here only few of the products only that information I want. So now only that information I want. So now here going for products inner join then sales

[6:22:57] then what method is on products dot p code is equal to sales products dot p code is equal to sales dot pers code

[6:23:10] that we need to go this is one Again

[6:23:28] name is unknown. What happened

[6:23:57] Products is spelling wrong. No, no. Spelling wrong. Products. See there. Here. Now we got a 10 here. That is so corn is the price is 50 20

[6:24:16] stock is there 10 has been sold see 50 into 10 500 pen price is 10 rupees and it quantity two items has been sold 20 rupees total stock is like this see then

[6:24:31] only what mother is kiwi has been given twice that mean two different customers has been taken this kiwi

[6:24:45] but whatever it is there so this is the product so and sales part product so and sales part now I want listen guys now I want three tables joining this is the two tables joining here okay guys just wait we go

[6:25:00] for that but uh one more point I want to discuss here simple point in this one here every time we used to write table name customers dot customers dotproucts dots sales dot something like that. It is somewhat

[6:25:16] cumbersome one when the table names are very lengthy. So shorter names is okay. So that these queries you can write on with table aliases

[6:25:28] with table aliases for a table you can have alias name. Okay. Now for this one you can have table alias names here. using the table alias table alias should go what is that table

[6:25:44] alias in the sense you have to go for like this so table alias in the sense for every table you take one or two alphabets as an alias name so for alphabets as an alias name so for products I'm taking P is an alias name

[6:25:58] products I'm taking 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 What that is stock for a sales you just to go for S dot quantity then S dot

[6:26:13] amount like this then what is P what is S we need to tell we need to tell that S we need to tell we need to tell that so how in the sense my here products as P inner join sales as yes so it is a

[6:26:30] 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 is an alias name here also you can keep like that yes okay then p something like

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

[6:26:58] names listen carefully that alias names so alias names are the important important and mandatory for joins mandatory in the sense even you already I have written at the top of that but if you come down to with this

[6:27:16] so your query becomes shortened that's it okay so I hope this is understood this aliasy names all queries you can add with aliyah's names the queries whatever we have chosen all these queries you can

[6:27:31] add with alias names only This is make it C dot star make it S dot star. So customers as C inner join sales as

[6:27:46] So customers as C inner join sales as yes then here S. C ID C. C id yes then here S. C ID C. C id see that with alias names it is

[6:28:03] guys that is so only we are making shortening of that guys please let me know I hope everyone is able to follow the table aliases right earlier we used to take we have been taken column aliases right now Here

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

[6:28:50] doing guys. Is it clear with aliyah's names?

[6:29:15] Yeah. Now, so here join with three tables. Three or more than three tables. But of course, we have a three tables is there. I'm writing three tables part. I'm writing three tables part. Guys,

[6:29:29] listen here. Three tables. So first of all, you have to make it observe that from the three tables. So two tables are parent tables. One table is sales, one table is sales. So when you are

[6:29:47] writing join query for a three table. So you should go for like this. So what is that? You know here select first you go to the C dot C uh uh C dot

[6:30:03] C name then C location. Okay, that is here. Now, two columns is enough for me. That for the products enough for me. That for the products table P dot P name.

[6:30:18] Okay, then uh what we go for the other columns here from the products table you columns here from the products table you take these all.

[6:30:42] P name has been done. P price P stock P stock quantity S quantity S amount has stock quantity S quantity S amount has been taken that and suppose if you want that ordered date and all

[6:30:54] what that is yeah order number ordered date also if you want you can take these date also if you want you can take these two columns also like this first you two columns also like this first you define it whatever the columns you want

[6:31:07] that is Okay. All the columns from the three tables that we took here. Three tables we took there. Okay. Let me do it one thing here.

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

[6:31:37] So you cannot make I already told you you cannot make join between customers Okay that one point you have to remember. Okay this is one thing just we go from customer as C inner join sales as yes

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

[6:32:05] C do C ID is equals to yes dot C ID. So 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 and products.

[6:32:20] One more join has been done with the sales and products. How it should done here? Yes. Here again you have to write uh inner join products.

[6:32:33] 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_C code is equal to S dotp code you

[6:32:49] P_C 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

[6:33:03] for products and sales then sales and customers anyh how you write it doesn't customers anyh how you write it doesn't matter okay now you see that now you'll get complete data here

[6:33:18] three tables join it is see there the customer stiffen is the customer has been purchased corn to it's a 50 rupees

[6:33:30] okay like 10 products he has been purchased The amount is 500. He has been ordered on this date. This is the order number. So complete details you have.

[6:33:46] is so products you just go for a kiwi. Kiwi twice as came because one one order from the moan one order from Oliver that is this is order number five. This is order

[6:34:01] this is order number five. This is order number three. You have like this here guys. Is it clear guys? Writing the query join query for three tables.

[6:34:26] Guys, please let me know. I hope that everyone able to follow.

[6:34:47] 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 the only attaching that is yes

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

[6:35:17] come to know but your understanding is fine that corrective 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.

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

[6:35:50] First what this sequence then afterwards this sequence sales to then afterwards this sequence sales to products sequence.

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

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

[6:36:37] here we are just checking on only sorry join checking on only join for example let's See that is I do one thing

[6:36:49] See that is I do one thing go to this product. Okay. report. Suppose if anyone ask that so that is

[6:37:03] here now show all the customer and products all the customer and products products of perfume category. Let's see

[6:37:15] products of perfume category. Let's see that is perfumes category if we write like this then how can you go so first rate enter join query see there now take this join query here and after this you add where clause

[6:37:31] where it's a perfume from the products one p dot category one p dot category P dot category is equals to what should we write meth that perfume Boom. All right. Where clause?

[6:37:48] right. Where clause? Yes.

[6:38:08] Hey, then spelling mistake is there. then spelling mistake is there. No. No.

[6:38:50] correctly. No perfumes has been sold here. See there. No perfumes is sold here. See there. No perfumes is sold here now.

[6:39:02] there. No. Yes. Now you write on there. That's Yes. Now you write on there. That's correct. Ma, no perfumes has been sold. correct. Ma, no perfumes has been sold. Snacks. Now you'll get it.

[6:39:19] See there snacks has been sold. This

[6:39:41] examples we will write it. First of all let's let us discuss all type of joints. Then we go for other examples including where group by having and all that is okay just I'm showing to you that is here now

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

[6:40:09] retrieving rows from joining tables rows from joining tables per matching per matching and non-matching.

[6:40:23] and non-matching. What that is matching and non-matching joining tables that goes here that is but again it has

[6:40:39] 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 this left outer join in the sense comparison from left side table or left

[6:40:58] all values of left side table of left side table to right side table right side table in join statement in join statement here

[6:41:17] in join statement here if value presenter uh in left side table. Left side table Left side table but uh not present in right side table.

[6:41:33] Excuse me. That row also retrieved. [clears throat] That row also retrieved. That is what we are have. This is

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

[6:42:01] So now here guys so let's see that is a left order join take the previous examples only so leave about three tables first go for so leave about three tables first go for with the two tables uh first one what

[6:42:14] take this is The take down to the products left order join. What is the question here? Now show here? Now show sold and unsold

[6:42:29] sold and unsold products info. Suppose products info. Suppose sold and unsold products info.

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

[6:43:07] I'm writing like this here. Okay. And here I'm writing in this way. Okay. And here I'm writing in this way. What is that? Uh lateral outer join

[6:43:20] sales something like this we are having. sales something like this we are having. Okay. Now let's see that just let's see let me take this one for an explanation prospection

[6:43:34] prospection in here this quiry you get it here that

[6:43:47] and what is the common column product code. No. Okay. Get the values from the product code. From the products table it is from the products table.

[6:44:00] Get all product codes. 26 products codes is there. Now you bring it this one here. Yeah, here. Yeah, this is the one. This is what here. Now

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

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

[6:44:47] one guys you see this line go for this line here uh for the word left outer join what is the left side left outer join what is the left side table what is the right side table

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

[6:45:17] yeah what method is products is the left side table sales is the right sided table is it correct

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

[6:46:03] 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?

[6:46:18] What is the left side table and right side table? cleared it or not? Guys, everyone

[6:46:43] productive, right side table is what? That is sales. That is sales. So, now one second guys.

[6:47:03] So what I explained here left outer join means comparison from see comparison means comparison from see comparison from all values of leftsided table to right side [clears throat] table in join statement. Here if values are present in

[6:47:19] 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 which means now you go to Excel product number one the comparison should go here

[6:47:36] 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 number one is there in a sales part. No, though it is not having the row will

[6:47:51] come with the null values. The product number two is there. Yes, it comes with values. Product number three is there. Yes, it comes with values. Product

[6:48:03] number four is there. No, it comes with null values. Product number five is there. Yes, it will come here. Product number six is there. No. Product number number six is there. No. Product number seven, no. Eight, no. 9. No. 10. No. 11

[6:48:19] seven, no. Eight, no. 9. No. 10. No. 11 is there. It will come. 17 is there. is there. It will come. 17 is there. Okay. This is then 16 also there. Okay. Then 20. What? Mother is 20 also we'll be having

[6:48:35] What? Mother is 20 also we'll be having that. here. So 20 20 twice is there. Now then like

[6:48:47] 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 So that is that means so here all values just see here 1 to 26 all values are

[6:49:01] just see 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 that is so that is the one thing just you have to go see that output mark left

[6:49:16] you 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 this is the sales part no quantity no amount

[6:49:32] cannot find only product table we'll find the details so that is coming there see totally wherever it is not having values in that will come into the nulls

[6:49:46] that will come into the null here matching non-matching both the values matching non-matching both the values are coming here guys getting or not my

[6:50:06] Guys, is this query is understood or not?

[6:50:19] Guys, please let me know matching non-matching. here? If it is understood then

[6:50:39] current the conditions has to keep it that but just when I'm writing only core line only no okay I'm writing only core

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

[6:51:13] queries. So same thing that here suppose if it is a right outer join is what if it is a right outer join is what right outer join.

[6:51:25] left outer join 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

[6:51:40] sided table so here so here here I'm writing right what I'm writing my right outer join I'm writing here. So here how the comparison

[6:51:54] goes in the sense if you go for that is the comparison from sales to products it 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.

[6:52:11] Sales to product in the sense where is that arrow symbol where is that arrow symbol not this is oh sorry yeah so here what is that we get it right now first 11. Yeah. 11 or 11 is there. 17 17 some 17

[6:52:30] Yeah. 11 or 11 is there. 17 17 some 17 will 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 but one will go. Three will

[6:52:47] 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 from right side table to left

[6:53:02] side table. So even though left side table having excess of values it won't be compared because comparison is going from sales because comparison is going from sales to product. So all values are completed.

[6:53:16] Obviously you get only sales report sold sales report. Obviously get what that is sold sales report you get it that is see there now

[6:53:33] so that sold sales report only you get that see you got only 10 rows you got only 10 rows here that is

[6:53:47] you got only 10 rows here that is this is here now right outer join sided table not present on the leftsided table it will bring if it is a right

[6:54:01] table it will bring if it is a right outer join. If it is a 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

[6:54:14] non-matching things okay all values of leftsided table will be compared to the right side table if it is left outer join all values of left right sided table will be compared to the right side table if it is right

[6:54:28] the right side table if it is right outer join And you change the positions of tables again the output will come reverse.

[6:54:48] there. So here now here you go for this and here what I'm taking you know

[6:55:00] and here what I'm taking you know this is I'm taking here this is I'm taking here and here I'm just going for that uh here now tell me the result now if I write like this

[6:55:16] if I write like this what is the result Yes.

[6:55:31] This query result is as right out join one 10 rows. Yes, exactly. This you make it left out join sorry position change my here.

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

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

[6:56:43] Yeah. Let's go for another uh join. Cross join is there. What is this cross join? You know uh so

[6:56:56] What is this cross join? You know uh so this type of this join is a join process join process. So without a join condition without join condition

[6:57:17] Here let's write on a query without join condition mark. What will happen if you write that?

[6:57:35] So take this is only take this process only here remove this join condition and here you write on cross join.

[6:57:51] here guys listen 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

[6:58:03] there in a products table? 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

[6:58:18] which one that Yeah. See the result of this one? Here

[6:58:32] So what happened here? What happened here? Ma, you guess that what happened? What is the result of this one? What happened?

[6:58:54] Yeah, multiplication has been done. 26 rows into 10 rows multiplied both 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

[6:59:08] all 10 rows of sales table. See there now here see the result. Yes these are the 10 is there. 10 is there. Okay that is one thing. Then one more

[6:59:23] Okay that is one thing. Then one more product then one like this we are have actually this is called here curtisine product. product. Cine product we can call that that means

[6:59:37] whenever you are retrieving data from multiple tables with the join process first what happens you know one result will be generated by multiplying one table rows into another table like this.

[6:59:52] table like this. Okay. Buth that is inconsistent data. From that one to retrieve actual rows we are we have to keep a join condition.

[7:00:04] We are we have to keep what that is join condition. That is the importance of 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

[7:00:19] this join condition here for this one you will get actual data only 10 rows you will get actual data only 10 rows you'll get see there you got the 10 rows only here

[7:00:32] even for the cross join so cross joiner is not useful for production it is not useful for production [clears throat] that it is the join has been given to does just to make you

[7:00:47] understand what's happen. That means what happens when you write a join statement without join condition in memory one table rows are multiplied with another table and one product's output

[7:01:02] will be generated that's called cartign product from that one in that one you have a consistent inconsistent rows from that one only consistent data retrieve that one only consistent data retrieve that join condition has to be kept

[7:01:34] condition there is no anything that except cross join. But cross join is not understanding perspective they have given that

[7:02:23] I'm not getting any responses from others.

[7:02:50] that's okay. Ma, then we go for right now. Self join. now. Self join. We go for what here now? Self join here.

[7:03:06] So what is a self join here? What is a self join my uh implemented on single table on single table when

[7:03:23] column to column relationship column to column relationship exist column to column relationship exist Self

[7:03:36] join in the actually see till now you are seeing that self join in the sense know at least two tables more than two tables also we have seen three tables four tables of course four table content will not get that is but self join goes

[7:03:50] on single table only one table on one table and one table how you write a join sir one table column to column relationship should go

[7:04:02] what that is column to column column relationship. Sometimes you will be having in a table one column to another column relationships are existed not only table to table. Yeah, for this yes ma'am go to the again

[7:04:16] old table. So we are working on emp table. No have you observed that emp table ma here this employee table last week we worked on it. Today also we worked for the subqueries. Whenever you go for this table here you

[7:04:33] have a employee ID column there and you see the last column manager ID. Manager ID is there. These two columns are having under relationship.

[7:04:45] Here it is a primary key and here it is a foreign key. we won't mention here. We won't mention here that. But uh the thing is right now

[7:05:00] employee id column same values are exist in our manager id column to whom is working under whom that is no relationship here. I I hope that these two columns are clear with you. Right?

[7:05:22] These two columns are clear with you that. So now we go for self join on this one. What is this? 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

[7:05:36] or their manager name. I want like that what I want in the sense of manager name 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

[7:05:54] who is working under whom that I want. right now. Okay. So, for example, Roy Collins is an employee who is manager E583. What is

[7:06:06] the E583 employee name? You just bring that one here. E583. Yeah. So, this is Roy manager is Jonet H. I want like that.

[7:06:19] H. I want like that. I want like this Janet H manager is 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

[7:06:33] write a self join 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 self join in the sense because you are writing join on single table again here

[7:06:47] you have to write inner join and outer join here we have to Use again what that is inner join or outer

[7:07:01] join that is one thing. Sir how you take one table as a two tables in the sense because we have a table aliasis there no table aliasis is there that based on the table aliasis you can do that what is

[7:07:16] that you see that how I'm writing the query here query here so select see there e dot first name you do one thing make it concatenate concat

[7:07:30] of this is the function both columns I'm concatenating Okay. Okay. That is one thing by putting comma then concat

[7:07:47] then concat of take one column as a manager. M dot of take one column as a manager. M dot first name comma m dot uh m last name as sorry. So here from EMP as EMP as E inner join

[7:08:08] So here from EMP as EMP as E inner join [clears throat] okay EM empore

[7:08:21] you have to write query this is the self join query join query this is what here now self join query it

[7:08:40] manager name this is okay you do one thing

[7:08:54] manager as manager now you took this See [clears throat] that Ry Collins is an employee here. Now he not given that

[7:09:09] an employee here. Now he not given that queria.

[7:09:21] is Janet Holland. Even if you want you can check that I I Even if you want you can check that I I removed.

[7:09:38] See rol is an employee. 583 is what that is here 583. Janet Holl is a manager. So Janet Holly employee manager is what? Cynthia Brooks. Okay. Cynthia Brook manager is what?

[7:09:53] Arther block. Arthur block it themsself is a that is the same thing you can is a that is the same thing you can check here.

[7:10:11] this is so John at holiday see Cynthia brokes so John at holiday see Cynthia brokes so lost we are having this

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

[7:10:54] and others also ma actually here I haven't get any messages or any responses from Sri Dvi Svi are you there in the meeting

[7:11:12] today are you practicing or not everyone ma But actually you have responded last

[7:11:24] week. No, today you haven't. That's why I asked you. But I'm very happy that if I asked you. But I'm very happy that if anyone everyone will ask some questions.

[7:11:39] So this is about completely joins chapter. Now 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.

[7:11:54] So very rare cases that when we are having column to column relationship between tables. So that we have left over two inner join and outer join. That outer join also left outer join and right outer join. That's it.

[7:12:13] hope that overall concept is understood right. Joins concept. So here primary key foreign key depends on that joins

[7:12:26] right is it correct primary key foreign key depends on that joins is there I hope it's clear with everyone

[7:12:43] this is the today's topic guys here now okay so on this topic we'll have some okay so on this topic we'll have some queries tomorrow first uh first half an hour then we go for new concepts tomorrow

[7:12:55] okay if you have any queries please let me know tomorrow's topic I'm writing my here tomorrow's topics are here uh what do you say that uh views is one concept is there

[7:13:10] views is one concept indexes is another concept then stored procedures Stored procedures is another concept we

[7:13:22] are having like this. No, no, not this one. Not stored procedures that is also will be there. Before that uh user creation, user creation uh when go to the user creation grant comma revoke

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

[7:13:57] things first we'll keep it one thing 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 the order we will make it this is the order we go for that

[7:14:18] is my very first one like we are having here so in our command cell 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,

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

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

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

[7:15:13] roll back are essential transaction language commands just see there used to language commands just see there used to manage data integrity by uh either permanently saving changes made uh made during the transaction or

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

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

[7:16:01] 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 difference is okay we will discuss that while working

[7:16:16] okay we will discuss that while working on it just come down to here okay uh this is okay fine that uh

[7:16:28] 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 EMP info

[7:16:45] so select star from EMP info this is the table just we are uh yes like we have some 3M this is n of the table and n of that okay so usually guys listen carefully here usually on this table that means you

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

[7:17:17] are called as a transactions DML transactions we are having. These transactions we are having. These transactions we need to control. These transactions we needed to control here. Okay, control in the sense for example

[7:17:33] you added some rows but within that rows some of the rows or all the rows made have gone some mistakes

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

[7:18:00] we wanted to go for that removing off all rows here that

[7:18:15] so I don't want that rows from the table so then uh usually what we do in the 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

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

[7:19:03] So there is lot of mistakes and insertion of rose I don't want it that so that I wanted to cancel that uh transaction last performed transaction I wanted to cancel just [snorts] listen my point that that

[7:19:17] is one case 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 to the previous state then I don't I

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

[7:19:49] how to get back those deleted rows see there now this is one aspect no sir I have the data entire thing is properly I've done it so that I wanted

[7:20:01] to made that transactions permanently in a database permanently in a database so transactions in the sense either insert or update or delete you remove some rows

[7:20:13] I don't want to get back permanently I wanted to remove it yes that is one thing I did some modifications I no need to get back the previous state I wanted to make permanent changes in the database that is one thing I added some

[7:20:25] database that is one thing I added some rows I added some rows to the table but temporarily has been added that I wanted to make permanently in the database then how so here make permanent changes on the database or make undoing the

[7:20:38] transaction just we performed so these two things are there that 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

[7:20:55] 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 undoing of that actions. So how to do that is okay for that one we have a two

[7:21:09] commands are there commit and roll back. Commit will make the permanent changes on the database. Roll back will make undoing the transactions just now previously what we performed before committing. Before committing this is

[7:21:22] very important. Once you made commit then after that if you go for undoing that it is not possible. Okay. Then again you have to go for other commands like delta or something that this is what but for this so

[7:21:35] if you want to use these command that means make permanent 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

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

[7:22:05] 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 transaction. So first of all that auto commit you

[7:22:20] 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 command is there. That set command we have to be used. So before starting your

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

[7:22:51] here uh we will make it that set auto commit. So Z auto commit command here that is see there now here this is set auto

[7:23:06] command part managing this one here turn on and 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

[7:23:19] equal to zero or on or equal to one has to be done that is first you have to make switch off this okay switch off in the sense now set auto commit equal to the sense now set auto commit equal to zero.

[7:23:37] it this is here now or else you just go for better zero. or else you just go for better zero. Okay that is one thing.

[7:23:49] Yes ma'am this command now we can work on the transactions. Now we can do okay now we can do transactions that so that is so now see there now you go

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

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

[7:24:33] Okay. So 20 is not there. So that uh make it 30. Make it warmer. That is 30 here. Now Make it warmer. That is 30 here. Now let's see that is a

[7:24:54] see there now so now the rows are added see that here rows are added here and if 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

[7:25:09] will get it that Right? See that it is showing old data and new data both are showing here. Okay. That means it has been saved in the database

[7:25:21] table but not permanently. It maintains. So what we can call memory in the caching from the caching memory these rows are there. So now here we

[7:25:33] have a chance either to make permanent these rows or else to make undoing these rows. Suppose if you don't want these rows if you don't want these rows here

[7:25:45] simply what you need to do it in the sense roll back use the roll back. There is another syntax simple that is and make it this is see roll back executed.

[7:25:57] Now you check whether those two rows are there or not in the database. See there those two rows are not there. So from the caching memory that rows has

[7:26:10] been removed. Just know what the operations you performed. Okay. That is so once again you insert it. it. Once again you inserted that.

[7:26:24] Yes. After this you must to do here now if you want to make permanent one in the if you want to make permanent one in the sense mother commit the commit has to go for it. Now commit will made that permanent changes

[7:26:37] commit will change that permanent changes on the database. Yes we have 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

[7:26:50] here. Just go to this one and make roll back. Let's see that whether the rows 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

[7:27:07] there. The roll back transaction before 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 on what we can call that uh updation for

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

[7:27:42] see I did like this here now so rama rama is there no ramani Ramanica. Okay. There something like

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

[7:28:08] see five rows are updated. So now whenever we go for this is see So now whenever we go for this is see they're now here. So, EM info whenever you go for this part here. Now, see that all has game ramandic

[7:28:24] but we have stopped the permanent transaction. So that right now here now we have an opportunity what is that made roll back.

[7:28:36] Okay. So now you see there the old data will come that see that that old data has came. So like this we can check but uh after update if you go for commit and then go

[7:28:51] for roll back it won't come the same thing uh we do that is let's go for that thing uh we do that is let's go for that uh delete from emp info uh delete from emp info okay you delete all rows no worries I

[7:29:06] 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 switched off that auto commit Now with the roll back we will get it

[7:29:19] Now with the roll back we will get it that rows. So now you made roll back all the deleted rows sir will be back into the table. See there now will be back into the table like this.

[7:29:33] will be back into the table like this. So you will be having that is guys are you able to follow or not? following or not guys please let me know

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

[7:30:07] commit and roll back okay so that is what so when we added a new row it will permanently to the table. When we modify permanently modifies when we delete permanently deletes that is it goes in this way. So,

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

[7:30:42] as one in MySQL workbench 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 is if we commit. If we committed deleted

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

[7:31:14] that here now. So delete from EMP info where EMP number is equal to 1,07. You go for that sig details. I'm deleting right now. Yes, I deleted. Okay, that is one thing. Now you go with

[7:31:30] Okay, that is one thing. Now you go with 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

[7:31:44] back. See there whether it comes or not you will come to know. Yes I did a roll back. After that uh just go to here. See the sic has came.

[7:31:56] just go to here. See the sic has came. Yes. Once again you delete. Yes I deleted. Okay. See in the database table not there. Now made commit.

[7:32:10] Now made what? That is commit. You made it. Now the row has been deleted permanently. Okay. Now try to get a roll back. After the commit I'm doing roll back. After the commit I'm doing roll back that I

[7:32:26] the commit I'm doing roll back that I did a roll back also. Now execute here. did a roll back also. Now execute here. See the row hasn't came.

[7:32:39] Roll back command works only before the commit. Remember that one point. Roll back works before the commit only. After the commitment, roll back won't work. Do roll back works only undo the last immediate transactions

[7:32:52] or how can I do two to three changes I have done using multiple commands? Yes, 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.

[7:33:09] Okay lot of things in the sense this commit and roll back just listen this commit and roll back uh I have been used till now on only one statement I used

[7:33:21] one insert then I shown a difference 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. Okay. Now see there here.

[7:33:36] Now see there here. Yeah. Just come down to here. [clears throat] table. So any table. Okay. Take that EMP table that.

[7:33:51] So on this EMP table first what I did you know that here insert then uh I did now here some modifications. Okay. Some modify. Then after that uh I did some delete or

[7:34:09] else I did insert and modify for example that means couple of rows has been that means couple of rows has been inserted and I have seen some data and on the data you modified it. Some of the rows where it has gone some mistakes

[7:34:23] commit. Okay. So if I go for roll back what will If I go for roll back what will happen in the sense because of you haven't done

[7:34:35] commit all rows insertion all modifications everything will go off completely wind up completely wind up that completely wind up that or else before uh roll back if you

[7:34:50] commit that that insertions and modifications what you've done that everything will be stay in the table permanently that everything will be stay in the table permanently that then afterwards if you use roll back it won't

[7:35:05] 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 new row adding modifying existing rows deleting some rows or something you did

[7:35:20] it any transactions okay after that you made commit everything will make permanent okay now that is one thing or multiple transactions What you do that is one thing but if you get back uh suppose if

[7:35:36] 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 that is going to be undoing here that is okay it's not part of one command or one

[7:35:52] thing that is here now I shown you one that you add multiple rows and you do you did some modifications and make it roll back everything will go off. Everything will go off that. So but if you make commit everything will

[7:36:07] permanently stays in the table. After that if you want to delete or something you have to go for a delete under commands that is we have to go for in that way. So that you should be cautious that while working on these two commands

[7:36:21] when should when it should go roll back that it should be like what you say that that it should be like what you say that is cautious

[7:36:42] 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 are having this there is simple two commands but before that what method is

[7:36:58] over here we have to make this one we have to switch off auto commit equal to zero when you switch off set auto commit equal to one then you have to go for

[7:37:10] equal to one then you have to go for that permanently doing that is but these things permanently In a work menu you can do some permanent settings on this even that update commands and all that is so where in the sense you just wait

[7:37:24] that here we have some settings is there in the settings part we can do in the settings part we can do yes ninas it's a temporary one only it's yes ninas it's a temporary one only it's a temporary one only that is

[7:37:40] that it won't be stay even suppose like see you are working on that you added some rows you added some rows that suddenly your system has restarted all rows will go off all rows will go off that that means all

[7:37:53] 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 course I hope so you would have enjoyed this certification course and also got a

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