--- title: 'SQL Full Course 2026 [FREE] | SQL Tutorial for Beginners 2026 | Advanced SQL Course | Simplilearn' source: 'https://youtube.com/watch?v=fMdyJxDz8Pg' video_id: 'fMdyJxDz8Pg' date: 2026-08-08 duration_sec: 27590 --- # SQL Full Course 2026 [FREE] | SQL Tutorial for Beginners 2026 | Advanced SQL Course | Simplilearn > Source: [SQL Full Course 2026 (FREE) | SQL Tutorial for Beginners 2026 | Advanced SQL Course | Simplilearn](https://youtube.com/watch?v=fMdyJxDz8Pg) ## Summary This video is a comprehensive SQL course for beginners, covering database fundamentals, MySQL environment setup, and essential SQL commands. It includes practical demonstrations on creating databases, tables, and performing CRUD operations, as well as advanced topics like joins, subqueries, and transaction control. ### Key Points - **Importance of SQL** [00:08] — SQL is essential for managing data in organizations, as highlighted by the US Bureau of Labor Statistics, and is a key skill for tech, analytics, and back-end development roles. - **Data, Process, Information** [02:53] — Data is raw material, process is the work done on it, and information is the meaningful output. This is illustrated with the example of constructing a house. - **Why Use Database Systems** [18:15] — Database systems are preferred over file processing systems (like Excel) for organizational data due to limitations in data storage, security, and data access speed. - **Database Definition** [25:59] — A database is a collection of interrelated tables of an entity, organized in a structured format. Tables consist of rows (records) and columns (attributes). - **DBMS and Its Operations** [40:38] — DBMS (Database Management System) is software that manages databases, allowing for CRUD operations: Create, Read, Update, Delete. It acts as an interface for users and applications. - **RDBMS and Relationships** [48:03] — RDBMS (Relational Database Management System) introduces the concept of relationships between tables using primary and foreign keys, overcoming limitations of earlier DBMS. - **Introduction to MySQL** [01:01:48] — MySQL is an RDBMS tool. It has two parts: SQL (commands) and T-SQL (programming). SQL is a universal language for databases, with 90% syntax common across tools. - **SQL Command Categories** [01:06:13] — SQL commands are categorized into DDL (CREATE, ALTER, DROP), DML (INSERT, UPDATE, DELETE), DRL (SELECT), TCL (COMMIT, ROLLBACK), and DCL (GRANT, REVOKE). - **Creating a Database** [01:09:08] — Use the command 'CREATE DATABASE database_name;' to create a database. After creation, refresh the schema to see it. Use 'USE database_name;' to switch between databases. - **Creating Tables and Data Types** [01:19:52] — Tables are created with 'CREATE TABLE table_name (column1 datatype, ...);'. Common data types include INT, FLOAT, CHAR (fixed length), VARCHAR (variable length), DATE, TIME, DATETIME, and BLOB. - **Inserting Data** [01:35:49] — Use 'INSERT INTO table_name (column1, column2) VALUES (value1, value2);' to add rows. Multiple rows can be inserted in one command. String and date values must be in single quotes. - **Constraints** [01:49:47] — Constraints enforce rules on columns: NOT NULL, UNIQUE, PRIMARY KEY (combination of UNIQUE and NOT NULL), CHECK, DEFAULT, and FOREIGN KEY for relationships. - **Update Command** [02:11:06] — The UPDATE command modifies row values. Syntax: 'UPDATE table_name SET column = value WHERE condition;'. It can also perform calculations on columns. - **Delete Command** [02:36:37] — The DELETE command removes rows from a table. Syntax: 'DELETE FROM table_name WHERE condition;'. Without a WHERE clause, it deletes all rows. - **Alter Command** [02:40:11] — ALTER TABLE is used to modify table structure: ADD, DROP, MODIFY columns, add/drop primary keys, and rename columns. - **Drop and Truncate** [02:56:04] — DROP deletes the entire table (structure and data), while TRUNCATE deletes all rows but keeps the table. DELETE allows rollback, but TRUNCATE and DROP do not. - **Select Command and Clauses** [03:05:42] — SELECT retrieves data. Clauses include WHERE (conditions), DISTINCT (unique values), GROUP BY (grouping), HAVING (conditions on groups), and ORDER BY (sorting). - **LIKE Operator** [03:54:04] — LIKE is used for pattern matching with '%' (any group of characters) and '_' (single character). Example: 'WHERE first_name LIKE 'A%'' finds names starting with A. - **Group Functions** [04:09:09] — Aggregate functions like MAX, MIN, SUM, AVG, and COUNT are used on numeric columns. NULL values are not counted by COUNT. - **Group By and Having** [04:16:40] — GROUP BY divides data into groups, and HAVING filters those groups. HAVING is used with GROUP BY, while WHERE filters rows before grouping. - **Order By Clause** [04:33:28] — ORDER BY sorts results in ascending (default) or descending order. Multiple columns can be used for sorting. - **Subqueries** [04:42:57] — A subquery is a query within another query. It must return a single value or single column values, depending on the operator used. Subqueries can be nested. - **Primary Key and Foreign Key Relationships** [05:14:42] — Relationships between tables are established using primary keys (parent table) and foreign keys (child table). This ensures data consistency and prevents invalid data entry. - **On Delete Cascade** [05:50:12] — ON DELETE CASCADE allows automatic deletion of child rows when a parent row is deleted. ON UPDATE CASCADE updates foreign key values in child tables when the parent key changes. - **Joins** [06:04:55] — Joins retrieve data from multiple tables based on relationships. Types include INNER JOIN (matching rows), LEFT/RIGHT OUTER JOIN (matching and non-matching), CROSS JOIN (Cartesian product), and SELF JOIN. - **Commit and Rollback** [07:14:49] — COMMIT permanently saves transactions, while ROLLBACK undoes them. These work only when AUTOCOMMIT is set to 0. ROLLBACK works only before COMMIT. ### Conclusion This course provides a solid foundation in SQL, from basic database concepts to advanced querying techniques, with practical examples and hands-on demonstrations. It is an excellent resource for beginners aiming to master SQL for data management and analysis. ## Transcript 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 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 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 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. Now before we begin our session, just a quick info guys. Simpan has got professional certificate program in data analytics, genative AI and adaptive systems in collaboration with IHFC and TIH of IIT Delhi. This program actually 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 capstone projects, plus access to tools like Tableau, PowerBI, AWS, Azure and Genai tools like chat GPT and clot. Learners will also receive a certificate from IHFC, TIH of IIT Delhi along with job assist plus for rumé support and mock interviews and career guidance. So guys, hurry up now and join the course. The course link is mentioned in the description box. Now before we move quiz to test your knowledge and the question is what does SQL stands for and your options are simple query logic 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 anyhow I'll explain it clearly. Don't worry about that. I'm not going to read anything here that. So okay while even if I'm having the sentences everything I'll explain it right now. Okay. So introduction to database is first thing. So some basics of actually we have to know what is a database. Uh why we go for the database these all things. Then afterwards we'll have some concepts of MySQL what is MySQL? The environment of MySQL how to install MySQL. These all things we'll come to know here. Okay, that is yeah guys see there the first of all the thing is right now here so what is a data what is a process what is the information what is data what is process and what is information first of all we have to know about these three all we have to know about these three points here what is data so what is points here what is data so what 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 very important and it is mandatory by knowing or unknowing it doesn't matter that whether you know or whether not to known that every process will go for with these three steps only data process information. 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 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 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 [snorts] of process again whatever the [snorts] of process again whatever the data we get again whatever the data we get uh that will be called as information that will be called as a information that will be called as a information that so here the best in a generic way in a in a generic in a generalized a simple example is right now there constructing a House constructing a house becomes we can call it as an all these three things. What is that? What is the data for constructing house? The material what you gather. So to construct a house, what is that material initially? What is the material we gather for that? First of all, there should be a place there now sand, cement, bricks and all whatever the material we gather to construct the walls that [snorts] everything we can call it as a okay like what you can say raw material or also even 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 taken randomly that there should be a process for that that means there is a plan to construct a house according into that plan the walls will be constructed or else so the pillars will be uh like constructed and all that everything we'll call it as a process that everything we'll call it as a process how we are doing that 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 constructed house is called as an information that constructed house is called information so 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 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 native languages, local languages, Telugu or Hindi. I'm a Telugu person actually. Okay. I'm a Hyderabadi Telugu person. So that uh Hindi, Telugu, English any like Canada, Tamil like whatever the speaking languages are there first what you have to learn in the sense now you have to learn some alphabets of that speaking language. So that alphabets everything called as a here data you can call. So after learning of alphabets what we learn in the sense of collection of learn in the sense of collection of vocabulary we learn some words. So after learning of words now we have some set of words is there. Now you have to construct a sentences. So if you want to construct a sentences using that word to construct a sentences using that word 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 once the constructor you are able to uh 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 sector See the computer performs any task for us. But the computer cannot performs any task for us without [clears throat] telling to the computer by the human being. If you're not saying anything to the computer, computer won't perform anything. Suppose I switch on the computer and I got a desktop. Then if I sit ideal, can it will it be done anything for us? What computer? No, it's not possible. 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. What subsha we're writing something on screen. we're writing something on screen. You want to say anything? that guys. Until and unless if you have a query, please send the query in a chat a query, please send the query in a chat box please guys. [clears throat] Thank you. 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 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 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 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 us here okay this is what just going to be taken okay but uh in a computer sector that means in IT sector there are two parts of working languages one is what here now learning programming languages guys I'm asking a simple question what we do with the help of learning programming languages I'm asking question who knows the programming language knowledge what can we do after learning the programming languages suppose someone is saying that sir I know Python so what can we do with the help of Python after completion of learning process Not only Python we have a so many programming languages are there in IT sector like Java, Python, net like so much of programming languages are there. What can we give that one? Yeah, current 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 with that instructions what is the 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 I'm not wrong that I'm not saying it's a 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 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 is fine. But doing that this task and that task and all that here now making that task and all that here now making softwares making software that is sur also saying that one like communicating with the computer it helps us to develop the software and solve the problems. Yes, that's fine. Yeah, that is what so learning programming languages. So learning programming languages in the sense right nowadays whatever the softwares you are using in a real world then in a society and by the different different organizations suppose if you go to the bank you will see completely computerized banking software computerized banking the every employees working banking relevant software if you go to the hotel supermarkets or some other any shopping malls you are getting computerized bills that means the bills generating by the computer and print will be given to us. Let's see that is anywhere you go and if you go for any other organizations there they are using the relevant softwares. So how those organizations is it readily available in the market to go and purchase that software and using into their computers in the sense no it's not possible like that then what will happen in the sense those all softwares has to be developed by using some x programming languages it might be a java it might be a net it might be a python or any other programming languages that is one thing so that uh learning programming languages used to develop applications or softwares applications or whatever that is softares 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 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 have to go for that database systems are have to go for that database systems are used to store data. What mother deserve securely? What mother is securely and permanently? Okay. Permanently that is. So these are the two things is going to participate the two things is going to participate in our daily IT sector activities. Okay. So programming languages requirement is one and database systems 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 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. 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 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 because it's the first session that's why I'm asking you that guys is everyone why I'm asking you that guys is everyone able to follow how I'm explaining that if anything uh it 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 two ways kind of dealing with IT sector. One is about the programming languages one is database systems where we store the data where we store the data. Here let's see that I have given a simple question and answer also regarding programming languages. What is a program? Now you guys has told that like giving set of instruction to the computer communicating with the computer that everything go for what is a program. So programmer 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 simply you can call as a a program that we can call it as a program that here. Okay, that's about irrelevant as for this topic because we are not concerned about the programming languages. So then one more question guys here. So why should we store some part of data in the form of tables? Why should we write a timet in the form of table format only? Why can't we write a 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 some language so second Monday first hour language second hour English third hour mathematics or something that so why should we write in a form of time table that yes easy to understand to get the data in a structured manner fine that is to store data in a proper success so that we can access easily when it is required. Yes, 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 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 can't understand. But 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 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 our database system stores in the form of data stores in the form of table okay that is one thing okay here so that's what just see there guys here now the same thing I have gone for that for easy representation accessing purpose table is used so database system uses the table format to store data so that now the continuation to this one data process information the next one we go for table so then you may come to know here s to store data 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? Do you know that guys? Guys, do you know data in the form of table except databases? Except databases. You are all giving data as MySQL oracle or something not that I'm not expecting that one here except databases. Yes. Naven yeses that is Yes. Exactly. Yes. Naven yeses that is Yes. Exactly. Yes. MS access, Excel and all is there. Then here question arises what is that you know anyhow we have some uh software tools where we store data in the form of table. Where we store in the form of table some tools are there here. Excel is saying you are saying Excel MS access fine exactly that only especially these two tools are the major tools that okay two tools are the major tools that okay but then why again we are going for database systems just now I told you that okay now so just now I told you that okay now so this is what okay so here why because in the sense of course so the current is saying something that's correct let's see that is so because of one thing that guys here so there There are three reasons here. Now the 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 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. 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 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 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 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 one lakh customers is 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 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 kind of Excel or MS access? Is it allows in the sense? No, it is not possible. in the sense? No, it is not possible. It is not possible here. So that is one limitation of the file processing. The second one is here. Now security file processing is not files are not having secured systems. So I can protect that file with the help of pro password in the sense 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. 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 you like I say a bank one lakh customer's information is there that one information one particular customer information I wanted to access based on account number or name or blah blah that is is it possible to get that using excel sheet in this of course it is possible I'm not saying it's not possible it is possible definitely it is possible but takes time takes time that only getting an account details. Suppose if a person goes a particular candidate goes who is having account and he will ask one statement of their account sir get me that last 3 months statement of my account then he has to gather the information from different sheets and information from different sheets and all that is very difficult it becomes very very difficult that and here like swagatika is saying that one duplicate data of course data might be going for duplications here so these are the Major three reasons to avoid the file processing systems in organizational data. Organizational data we are talking about organizational data. Listen about organizational data. Listen carefully that organizational data. Okay. So that these all will overcome the database systems. These all overcomes what that is database systems that here now. Okay. See so these are the limitations here. Now from that limitations a database systems will overcome that se so what is that say a database is a better for managing large data sets complex data relationships or when multiple users need to access data access and update data simultaneous. So this is the answer for this one. DB will provide full security of data. 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. So don't think that is 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. And I wanted to manage in in Excel sheet. It 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 any small scale organizations is the Excel and MS access is useful perfectly useful but the large scale data storage for organizations when that is I have given you only one lakh customer for ICA bank but really is it is the bank having one lakh customers no some cr of customers is there or else you take another industry like take a social media accounts Instagram Facebook is that Instagram details Facebook account holders having the data in Excel it's very way it's not at all possible that like education system universities that like education system universities school is limited but board state board central board universities 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 data that is what we should do. 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. So in a general way what is a database in the sense of we can say that like this it is a collection of raw material or facts and figures organized in a structural format. What is the structural format here? Now table of an entity 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 interled tables of an entity. So like that we can say that is so the entity might be any school or any organization any form that a banking system school college or any insurance department a medical department medical department hospitals or else any like supermarket one medical stores whatever it is you can take that is that is called an entity here. So for that how the data will be stored. See that guys for that I have taken here now databases student sorry databases school database I took here. Now I simply know and simply roughly I just categorized like this basically school having students employees and transportation. kind of transportation library these all 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 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 this is there if it is personal info then what will be the details we gather for that see there now registration number, student name, now registration number, student name, father name, gender, age, class admin. That means admission has been taken for the class location and what is the role student. Registration number is different from role number. Okay, that is one thing. Then when when he's joined to the class that then class details should come here. So role number, student name, class, which info, role number, student name, section, days working, days present and in the month of that month and year or month, month and year you can say that is like you can have examination info, marks info, fees info, games and info, marks info, fees info, games and sports info like that relevantly student 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 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, deductions, okay like loans whatever it is there that is called salaries info, paylip part. Then when it coming to the library 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 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 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 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 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, let's see that is so whenever you wanted to have that. say that is welcoming questions here. Yeah see there as I told that one see an entity a simple definition part you can take down that here. take down that here. Yes. You see this one here? 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 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 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 see that guys one thing if I'm saying a person you tell me that what are the attributes of a person having general in general you and me personally sense human being you and me are the persons that now tell me every human being that now tell me every human being having what are the attributes guys please let me know that yeah karan you and svi Can you tell me that what are the attributes? Yeah. to identify the name to identify the person 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 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 everything 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 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 school database. Okay here now. So a database is also you can call it as an a 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 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 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 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 record we can say that is for example let's [clears throat] say that here guys so now let's see yes suppose I'm taking now role number is one column then student name is another column gender is another column age is is 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 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 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 that attributes. Okay. That is one thing. 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 those all call it as here record what that is record you can say that is this everything will be considered as one record or a row this is everything okay so that is one thing so here a role number is an attribute ute and its value is 1. Student name is an attribute and is 1. Student name is an attribute and its value is ken. Okay. So gender is an attribute and sorry yeah gender is male is its value. So like that you will be is its value. So like that you will be getting here that is record or rows. And here one more also we can go for that here this is called here record we can say that is yeah this is one entity that completely record one record or else we can also going for this is a row you can call that one what mother is row also you can call it that is like you have so you can call it that is like you have so much of student details we entered here so much of student details We enter like this there. We enter like this there. Okay. Like what we can call that is so like this we can go for it in this way that Yes. So like you can add it this. So I hope everyone got it this one the differences between attributes and rows that so here one record of the student is an entity in this example. Yes, 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. Guys, please give your answers. Please say yes or no. Even if you have any queries, please post it on the chat box. Guys, 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 you guys are having almost 30 candidates in the session. 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? 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 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 now what is the database we know now what is the database and different an example and its attributes blah blah something is there but how the databases can be created and how the databases can be worked and all that is yes there should be some software tools there should be some software tools are there okay within that software tools the very first one and very beginning level is right now there what you say in the sense so DBMS is there guys listen there this is what just we 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 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, retrieve, update, and secure data efficiently and consistently. handling large volumes and multiple users simultaneously. That is what a DBM 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 we can say that DBMS is the software tool for working with the databases. DBMS is the software tool for working with the databases. Within that working everything will come. What is that? So storing data, modifying data, removing data, retrieving data, lot of activities we do, lot of activities we do that is within that lot of activities. So more mainly four categories of actions are there. First one is creating, how to store that is one thing adding data you 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 retrieve that data very quickly that is called reading reading retrieving data. The second one often we perform the third one often we perform that how to modify data if required for a student's data. For example, your volume. That is better because myself I'm speaking somewhat loudly myself I'm speaking somewhat loudly only. How about others guys? I'm 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 database in the sense method so it's a software tool it's a software tool to work with the databases within that working so like storing of data then what we can call retrieval of data modifying of data deleting of data the modifying of data deleting of data the blah blah will go but on that blahla the main categories are here now report. One is storing, adding data to the table or adding data to the databases. That is one thing. The second one which That is one thing. The second one which is very important when retrieving data from the large amount of data. Let's say that a banking system having one lakh customers from that one lakh customers one customer information how to get 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. 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 deleting of that customer details from that bank. Deleting details these things will happen here. Now, so that simply you can call it as here code operations. What is that here now? Code operations is there. So C stands for here create for storing. U stands for here now update. Then R stands for here now read or retrieval. Then D stands for here now delete. D stands for what? That is delete. Mainly we focus on this. These these are the operations that but these will be done in a different different ways that is what okay so now to 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 defined by MF chord. 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 thing just I'm forever information I'm just giving that 14 rules will exist that okay so that is one thing so what are database software tools guys here now let's see have you heard about these names guys have you heard about these names DBS and Fox Pro. Anyone have you heard about those names? H would be fine. Nice. Sounds good. that. So if it is because you guys are saying So if it is because you guys are saying 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 systems in the world is dbase then modified version of that database system is right now that right there pro is there those are the very fundamental very initial days of database systems those two software tools are now one is available but no one is using that is okay later on that we got Oracle SQL server MySQL postgrads SQL DB2 there are so much of database software tools are there okay 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 DBN has been evaluated evaluated in the late 1970s or 80s that is exactly uh we don't need that exact year or something that is okay but right now we something that is okay but right now we are in the 2026 we are in 2020 this is all about 50 years lifespan is there again even if you go for 1980 so that is 20 years this is again 26 or 45 years so this lifespan is 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 different prospections and got renamed in a different ways and added some additional features to this in that prospection. Okay. Here first modification first recognized modification of the DBMS is RDBMS relational database management system. We can say that what is meant by that relational database management system. Okay. Here so the relationship between tables 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 okay in this marks table I'm giving uh 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 that is whose marks it is I'm going to introduce here now a column called role number a column called role number that is and a column called role number that is and Let me add two more students here. Oh serial number is one that then exam is for example let's say that quarterly for 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 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 rule. That is my rule here. These marks for That is my rule here. These 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 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 this way so [clears throat] 130 is the answer suppose if I give 1009 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 accept the role number which is not present here since it is excel okay we are able to since it is excel okay we are able to give okay but as for the database systems it should not 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 of this role number column that is whenever we make a relationship between 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 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 so it is not possible. So that everything has to be taken care by has to be taken has to be taken care by has to be taken care by like user only that is user care by like user only that is user only. So 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. So under that uh these two softares will work dbase and fox pro will work that is dbase and fox pro will work but later on that that necessity has been recognized and the relational process the relationship process rules has been added to this DBMS then the has been added to this DBMS then the name has been given for that RDBMS only one feature I explained here in excel okay relational regarded 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 RDBN relational database management system. In this relational database management system we got a concepts called primary key foreign key concept. The foreign key is the concept has been introduced making relation between tables to avoid making relation between tables to avoid the inconsistent data or duplicate data or something else. Okay, that you will come to know while working on that. come to know while working on that. Okay, so that is the one here. Now RDBMS guys here now relational database management system swagatika so I'll provide it everything that is okay you will come in the sessions that no worries okay now just follow this one even if you want you can note it 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 this modern database management system in the sense It is perfectly suitable for a present trend. Okay, presented trend that is how to communicate multiple databases which is located in a different servers. how to communicate multiple databases which is located on a different servers for example let's say that one more example I'll give you here uh yeah you take a banking system of multinational one suppose I say a bank is having in multiple countries multiple countries that is so how abroad customers will communicate into Indian branches or else how Indian customers are communicate with abroad branches Suppose if a person has been taken account in abroad they will maintain their own database system separately in their country Indian branches. So Indian customers having their own database customers having their own database systems in Indian branches only that is but having accounted same bank. So that might be have some transactions between one country to another country then how database systems will communicate for that one there is some kind of rules and rules has been developed which is called 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 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 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 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 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 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 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 under the RDBMS only like we have SQL server, you have Oracle, we have MySQL, Postgres SQL, DB2 and all that is we will work on those things only. Okay. Okay, when that situation so how to communicate multiple databases which is communicate multiple databases which is located in different servers then the 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 the form of object then RDBMS concepts 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 yeah so one thing ma one let's see okay very simple thing that this is one database database here now. Okay, you take local itself now database of ICICI 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 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 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 you going to withdraw the amount by going to same ATM center same bank ATM going to same ATM center same bank ATM center. irrespective of the bank we go to that ATM we insert the card and we withdraw ATM we insert the card and we withdraw the amount. Okay. But how for example now I have I 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 the transaction has to come from the ICA bank not from the SBA then he will come bank not from the SBA then he will come to how it cost here yes so here bankto bank communication will be there what that is bankto bank database communications will exist bankto bank communication will Oh, you are doing transaction from the SBI bank ATM. Okay. But your transaction will ATM. Okay. But your transaction will happen only on the IC bank. Same vice happen only on the IC bank. Same vice versa. Same vice versa that is. So now 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. 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. 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? placed in different servers placed in different servers placed in different servers that so what are the rules we should follow in DBMS prospection in what that is DBMS prospection those rules all comes under the MDBMS rules all comes under the MDBMS I hope you got my point right now guys. Anyone having any other queries guys? 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 with the MySQL because we have a so much of software tools are there. Verac SQL server, MySQL something that is here. What is a MySQL? What is MySQL? Simple thing guys. So it's one of the 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. 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 are two parts. There are two parts is there. One part is SQL part. One part is SQL part. Second part is programming that is a TSQL part. Names might be different in other software tools but there should be a programming also inside the databases. Okay, that is one thing you have to keep in mind. That is one thing you have to keep in mind that is so that SQL all the commands will be there TSQL transacts SQL programming part will come. First 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 or SQL server postgress SQL or DB or even you go from postgress SQL or DB or even you go from the DBS FoxPro also it is common might be having some little syntax differences between one software to another software that two 90% 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 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 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 olden days where it was working in the sense of at command prompt sense of at command prompt per minute at command prompt that okay in that way it goes there. So SQL was using at command prompt later do on developed go environments for different companies. So actually it was developed by ANC. It was developed by ANC. But nowadays so it is using no but nowadays ANC's company not using the SQL because they have been sold to different first that has been sold to Oracle. Oracle has been take over the has been taken that SQL and further to they added environment they have added programming part and all and they released as a Oracle. Then they took that Microsoft people. Then it has been developed surrounded to that and developed one product called SQL server. 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 download get the like license from the ANC company about that SQL then you get your own environment and you add your additional features for that and you can release yourself one SQL tool yourself one SQL tool like that. So SQL is full of commands. like that. So SQL is full of commands. SQL is full of commands that okay how those commands will come and all will be coming into this one here. 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? 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 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. 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 there. Data retrieval prospection only one command is there. Select retrieving data. Then a transaction control language commands are there. Commit and roll back commands we have that. Okay, that is then data control language commands are there. So giving permissions, withdrawing permissions between the databases and all that. So between the databases and all that. So grant and revoke will come there like we have 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 of that MySQL. So topic wise the commands has to be discussed. Topic wise commands has to be discussed here that is okay. So then we'll come to know first we have to start from here. Okay that is but before that we have to Okay that is but before that we have to see the environment of MySQL part. Okay, this is the commands part we entered. But we go here now environment and all that is. So the very first query what should be right here and how that is in the sense first of all we have to create one database. So from here you will be getting that uh 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 as an 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, 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 is the command we have to go for that from a 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 writing here. Guys please follow that. So I'm taking now create So I'm taking now create space database space 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 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. 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'll get uh one message and green color tick mark that means that database has been created that command has been executed successfully. Okay that is the confirmation we got it here. Now where can I see that database in the sense guys? So I hope everyone is seeing here administration in the right left side panel administration. You are seeing a everyone okay everyone seeing this administration beside of that only one arrow mark will be there. See there 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 section. Left side arrow mark is there. If you click that administration, if you go to here, this one schemas, once you go to here, this one schemas, once you get this schemas here, you have some get this schemas here, you have some schema sys is there. Below of the sys, schema sys is there. Below of the sys, below of the sys, right click on it, right click on it. Go for refresh. Whenever you go for refresh, the database what you created will make visible here. will make visible. Do it that guys right now. Do it that guys. So first what should we do? Ma you go for type the command create database database name any name you can give. I have given Feb 2026 DB you give any name test DB demo 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 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 the right arrow button is there you click on this right arrow button you'll click on this right arrow button you'll get like this and here sis database will be there. So below of that right click on it and refresh the database what you created that will visible here guys please confirm that how many of you succeeded please confirm that how many of you please confirm that how many of you succeeded that is everyone visible that is yes yes chendra Praash what happened to you? Just let me know. what happened to you? Just let me know. Is are you facing any issue? patience. So once we settle on this 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 zooming? Can you decrease the screen zooming? Screen zoom. No. No. Maximize it first. Maximize it. Maximize. it's not getting maximize. So go for control minus on your keyboard. Controll minus on your keyboard. Hold on the control key. Click on the minus button. Control hyphen hyphen button on your keyboard. You do it that your keyboard. You do it that control minus the control key and click on the hyphen button. hyphen button so that your screen zooming will decrease. Uh okay wait okay okay but don't do anything just wait now there you were saying administration no in the left saying administration no in the left side uh then click on that arrow button side uh then click on that arrow button uh right click on white area white area not that one in the below sis below below of the sis yeah refresh yes you got the that is keep like this on your screen okay stop sharing now you on your screen okay stop sharing now you got create one more database. You create one more database. Now I'm also doing that guys. Going to create a database test DB like this. You create 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. What is that? Create database is the command or it's not an attribute here. command or it's not an attribute here. Database name one I shown you know that see there it is a database name. See create database the database should be create database the database should be identified with one name database name. Okay, that is the name of database should be given. should be given. I hope you got it right now. refresh once again. So two databases has to show like this on your systems, 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. Now you have to decide that which database you have to work here. You have to choose that is so how to choose that on which database you have to work in the sales guys one more command I'm introducing right now here use 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 I'm doing like this what is that so suppose if I want to work with the first suppose if I want to work with the first one use FEB 2026 DB should go and select that command. should go and select that command. Select that command and uh click on button. So that you see there that the database has been highlighted here 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. 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 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. Now sir I want to work with the FB DB. Okay. Select that and execute here. This will be highlighted. Now you create any tables, views, indexes, procedures or something. everything will go on stored on into in this database. on into in this database. So now my point is here use command is 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 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 work. So then within that tables views and all So then 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 results command everyone seeing the same results guys. 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 What next? In the sense obviously in the database actual data will be stored. Listen guys here now actual actual data will be stored in tables. Actual data will be stored in tables that. So now here we have to know how to create a table. We have to know here how to create a table. Let's see that how the table will be created. Let's have now some explanation of the create table now some explanation of the create table command. Okay, let's see there 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 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. 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 row number is one column name. Student name is one column name, gender is one column name, age is one column name, location is one column name like this. So we have now some columns. But whenever you come down to this column, all the numbers we are entering. Whenever you go for this one, names we entering. Whenever we go for gender, again text has been entered. A this is again text has been entered. A this is numbers again location text like this. If you have a birth date, date values, okay, birth time, time values, something will have like this. So when it coming to here, see there, first you need to to here, see there, first you need to mention the column name and it's a data type has to mentioned that means what type of data you are entering into that. I'll tell you what are the data types having in MySQL. Then one more is there here constraints. Guys remember that some of the things I'm enclosing within the angle brackets and here I'm enclosing square bracket. Square bracket in the sense this part is optional. If required we can use if not optional. If required we can use if not required we can avoid but whatever I required we can avoid but whatever I enclosed within ankle brackets that must enclosed within ankle brackets that must be given that must be used avoid it that be given that must be used avoid it that is the meaning. So now here column table name has to be mentioned. Then list of column names has to be mentioned. Along with that the data type also has to be mentioned like this. All the list of columns whatever you want you mention it and you opened a bracket here round parentheses go for closing of that and put with semicolon. Okay. Anyhow we write right now but what Okay. Anyhow we write right now but what are the data types are existed? Yes we'll come to know here the data types. See there these are the different types. See there these are the different data types existed in database. data types existed in database. So there is one data type called int is there. What is int here? If it is int on that column all whole numbers will be entered. Whole numbers that means like numbers Whole numbers that means like numbers 1,00 1 1,002 1 2 3 minus values - 10 - 20 that mean the number which is not having decimal that's it 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 that then how can I enter the text so text car is there car 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 where car one more also there 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 date value only that while entering the date you have to go with first year then month then in this with first year then month then in this format you have to enter that then time is there time values only time also having format same thing regular format hours minutes okay now like here 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 in a database system, so other than text to images, if you want to store in a video or audio or something that cannot be stored to direct in video format or audio format that should be converted into the binary format. So to store the binary data we have lo BB 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 image also little later. Okay, even this image also you forget about that. Okay, first we will get the knowledge on these data types while creating table. types while creating table. Okay, that is so now so how to create a table by using this in a sense. So of course here we will be having one table creation. You see that guys here let's take this is okay first of all let me go for explaining about one thing what is that you know okay anyhow you got this table here this command okay see there now std name is there std name so in this std name see that is nothing but student name it see that is nothing but student name it is see there std name car of 20 I have is see there std name car of 20 I have given and sometimes I'm giving std name given and sometimes I'm giving std name okay work pair of sprint so what the difference between these two here both are entering for text only what is the difference my here fixed length and variable length okay now tell me what is the so here of 20 is what in the sense in std name column maximum you can enter 20 characters. That is size of that name. Size of the name or else length of the name maximum 20 characters we can enter. How many characters? Maximum 20 characters. More than 20 characters name 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 here I'm giving now my name. Let's say that vayu gopal I used. Let me know ma how many characters are there on that vein gopal. Tell me how many characters are there on the way goal. Guys please respond. the way goal. Guys please respond. Nine is there. Okay fine it is nine. Maximum how many characters we have given? Maximum how many characters we have given? 20. So how many characters are free space right now? How many characters? Three 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 know in storage not explicitly listen carefully in storage in a database storage even though see in car in a memory that 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 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 other data into that in a memory not explicit implicit memory storage but whereas when coming to here work of 20 maximum you have given 20 characters but how many characters be utilized in the sense 9 character space utilized only 9 character space will be occupied in a memory storage remaining 11 character space will left over for further usage can further data store usage that is 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 will occupy 20 characters but here four characters space one occupied remaining 16 character space will be toed 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 memory will be occupied by the work. 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 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 fab 2026 DB yes [clears throat] so within this I'm writing now create so within this I'm writing now create table here just see so what is that create table 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 continuously write my but I'm writing in multiple lines so now here I'm taking row number is the column name and int is the data type and put a comma yes one column has been done guys listen carefully table names column names must be single word multiple words you should not use here. Table names and column names should not go for multiple words. There should not be a space between uh table names or column names. If you want names or column names. If you want space, so you have to go with a symbol called 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 name as a std name. Then here I'm giving here vector of 20 characters. That length is up to you. Okay. Then I'm giving here now birth date. So it's a date. So it's a date. I'm giving that is then location. I'm giving that is then location. Then I'm using here now car of 20. Yes. So then put semicolon. Yes. Like this you have to write a a simple table create command. So then what should we do? We have to select these all lines. Lines all lines you select and go for execute button. Yes, I got executed the table. Okay. So where can I see this table here in this database? You have a tables part is there. Now 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 on the arrow mark. See you will see the buy data table within that click on the arrow. See that columns. Click on the columns. The list of columns what you mentioned will be shown here. Then indexes foreign keys triggers is there. 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 small letters that means in which combination you create table names and column names in the same combination you have to use here that is there is any restriction at all even when you write a small letters also you see there small letters also you see there so whenever you come down here create table see that I'm writing create table emphas 20 or 30 characters. Then here I'm going for job. Okay, where care of some 10. Okay, that is one thing. Then uh we can go for that salary it is also we can make it float or else make it int only. No worries that even if it is float nothing nothing to worry. Yes like this and I'm selecting this and going for uh executing say I got executed the table. So in a test DB I'm going for refreshing all this. Okay you see there here. So you will see here now guys you see that the small letters only. So here it is capital letters that only. So here it is capital letters that is up to you but my suggestion is that you might be asking sir which one should follow better follow capital letters better follow capital letters that's it so there is any restriction at all whether writing small letters or capital letters anything is okay so what is another one that here now wret again getting an error what happened ward getting an error what happened ward share your screen. Got it. Nice. Nice. Nice. Sounds good. going very slowly because you have to settle down here environment and settle down here environment and executing queries and all that. So that is who already having knowledge be patience in a session. be patience in a session. So shall we go for I'll show you just wait okay first of all basic things we are working basic things because I told I'm repeating once again that it's a first today so that every day one itself I can't go for all at once I'm taking slow by one by one at once I'm taking slow by one by one one by one yeah guys is it okay Suraj >> Yeah. So now we have created two tables. Okay. In a two different databases. 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 there? No records are there. So how to add the rows? How to add the rows here? This is very important. Let's see. So for that we have to go for Let's see. So for that we have to go for some other command. Okay. What is that? some other command. Okay. What is that? See how to add rows to the table. 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 there insert. See guys. Insert into table name. insert into table name. Then to which column you want to add the data that column names we should get that is column 1 column 2 or something for that columns we have to or something for that columns we have to go for values val one val 2 something we have to be entered here. This is in this case wherever you are This is in this case wherever you are having number sorry uh strings and date having number sorry uh strings and date values string date values and all has to enclose in single quotations single quotes quotes single quotes has to be taken of that. Okay, that is one thing we have to go. So let's see that how this insert command has to get it that is see there. Now we got one insert command for the bio data. So the 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 where that is I'm going to this 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 FB 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 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 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 mah then go for values okay this is and first what is the column role number give any role number here. Okay. Any name you can give it that is 101. Put a comma. Then 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 write down that is here. Now so here I am writing that Gita Sahastra. Yes. Put a comma. Then birth date. Date also should be in single quotes. But uh one thing you should remember that what is the date format we should give for the birth date guys first year then month then day we should not forget that so that here also single quotes so that I'm giving right now 20210 I'm giving right now 20210 - 9 - 12 that is one thing then location that is also a string so that Here we go for that provid so entering data is not a case sensitive data part you can give anything that is I'm talking about only commands part capital letters are anything so data anything you can enter suppose here let's see that is how I wanted to go for like this you see that what I'm doing like this you see that what I'm doing you know first one uh so here Gita you know first one uh so here Gita okay sahastra draw we'll take no worries. Yeah. So now select those two lines. Select those two lines and go for execute. Yes, it got executed. It got executed here. That is that row is visible on the table for us in the sense it is not visualizing. in the sense it is not visualizing. It is not visualizing here. So one thing so for inserting of rows we have used one command insert that is okay but to 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 have to write like this. Select star from bio data has to go for it. Select from bio data has to go for it. Select that and click on execute button. Okay. You will see that data. See there. Now this output should come here to you guys. Work on it. that yeah see these two seema the screen which you are seeing now try to execute that one first go to your database where the by data table is there where the by data 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 give yourself Anything do it that 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 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 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 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 we 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 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. That is a rule for MySQL. 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. 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. 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 suppose if I have 10 students data for example let's that so should I need to write 10 insert commands in the sense of course if you write not a problem for that that is but with one insert command with one insert command And multiple rows can be added that is only one row. Now multiple rows can be added here that is so now I am writing a insert command like this here. So what is that in the sense just wait uh sense just wait uh uh yeah see there here so what I'm writing you know take this insert command write down 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. birth date. So comma location. So then I'm writing here values. So 1,002 then here so prai then here now then here now so 2012 - 0 7 - 20 so then here that is so so again what that is add just I'm yeah one again what that is add just I'm yeah one is done then put a comma put there comma and once again you write like this what is that 1,00 to guys wanted I'm giving to show you one uh mistake that is again already one uh mistake that is again already given role number so then here so here given role number so then here so here now I'm giving vanilla yeah vanilla I'm giving that so then here what is that in the sense 2015 hyphen 10 - 10. So then again here something like I'm giving nellor okay that is one more location yes so okay that is one more location yes so then one more I'm giving that here so then one more I'm giving that here so here 104 I have given that so three rows Just 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 selected those three lines and see I got executed that is see three rows are executed that is see three rows are affected. Now you see here that is so select star from buy data and go for this one. See you got the data here. 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 see that now data all rows you are able to see space problem that is but you are having the data okay like this multiple rows can be added but here one problem is coming so wanted did a mistake like 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 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. Okay, that is so no constraint so that it has been accepted the duplicates no 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 guys same thing will you do it that on your side do it that how to write multiple rows here by seeing that you just see that ma arun joti What is that? If I use car type then need to mention the length also. Of course whether it is a car or where car length must be mentioned. Now that is compulsory. I told you know that you see here you see here see ari see this excel sheet the same thing the column specification is same thing for the length if it is a string thing for the length if it is a string whether or it doesn't matter. 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 rows. So, now what will be there in the sense? Yeah, I have shown you one small issue. What is that? I have given a duplicate role numbers that table has been accepted but it should not accept. It should not accept that. Okay. Then how that can be taken up? How how can you avoid? Yes, actually always tables you avoid? Yes, actually always tables has to create with the help of what do you say in the sense constraints only since it is an optional but we have to make it that is a mandatory. So first of all what is a constraint and how to work with the constraints and all that is let's see now here actually we that is let's see now here actually we go for like this here one thing so what is that you know let's make it like student is the table name okay here a role number is the column let's see that is here now the st name is another is here now the st name is another column like a is another column 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 this it is a data type is int. And for this here where care of some 20 or something it is int. It is car of one only. I want to int. It is car of one only. I want to represent gender male or female M or F. represent gender male or female M or F. Then here it is where car then but on these columns I wanted to put some restrictions that means in the sense some rules what is that you know okay here it should not be duplicate duplicate and should not be what my null empty that is one thing we have to go student that is one thing we have to go student It should not be It should not be null. That is one thing. And uh it should be it should be between uh 20 to 30 years only. Gender it should be Gender it should be should be either Y or Y only that is location by default I can hydroad like this I wanted to have some restrictions that is see for every column I have given one rule so these rules actually called as a constraints while creating table. So there is a while creating table. So there is a concept called constraints is there. There is a concept called constraints is there here. So these are the rules to implement on table columns. While creating table while creating table for every column if required for every column if required we can place some conditions. Those conditions are called as constraints. What is the first one as constraints. What is the first one you have here in the sense null see it allows to empty values in a column it is a default constraint nothing but 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 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 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 here see that it is a combination of unique and not null constraints. unique and not null constraints. But for every table per see here only one primary key will be allowed in a table. only one primary key constraint can apply for a table. Okay, that is one thing. Then check it allows to provide specific values or range of values. It allows to provide specific values or range of values. That is one thing and we will write down that default it allows to set a default value for a column while creating table. That is one more and foreign key it is used to make a relationship between tables relationship between tables that so that that is the relationship part you forget about as of now see null to 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 shall we fulfill these things here now we will fulfill these things See what method is role number. It is in it should not be duplicate and should not be null in the sense. So here you will get a primary key. Primary key we will 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 method is should go to not null. What method is not null is see it should be in between 20 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 had in the sense no default constraint so these constraints we have to set while creating student table as of now okay this is So let me write down this one here. So how can we write on that 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 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 into. Now you specify the constraint after data type constraint has to be specified. If we give the primary key that will not allow you duplicate values and null values. Okay that is the second one. What is 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 yes gender one more constant. Now yes gender it is care of one one character. So how to apply the check constraint here? It should be either M or F. Other than these two alphabets, any other alphabet you have given, it should not accept. Then you have to write a check constraint like this. What is that? You 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 single quotes. Okay. Now M single quotes. Okay. Now M R again gender is equals to Okay, f you should give then bracket close and put a comma. So that is one thing we have to write down. Okay, then is down. Okay, then is so is it is what here? Int. Then again so is it is what here? Int. Then again for this also check check is greater for this also check check is greater than or equal to 20 and is less than or equal to 30. Yes. Then location so it is worker of 10 or 20. So default I'm giving that hydroad. Okay then go for closing and put 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. 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 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 date another one is student table so click on the student table Okay. Now here columns will come 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 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 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 hydrobat only then you can skip the location column remaining columns you can addit that that is suppose if the student location is not hydrobat then you add that location column and insert row. So now see there here I'm writing now insert command insert into student. So I'm giving role number then std name then gender then age that's it location I'm 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 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 guys you want to see that one go for select star from student guys the 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 actually in insert command in insert command we haven't added location but we got the location hydrobat because while got the location hydrobat because while creating table we made that hydrobat. creating table we made that hydrobat. Okay, that is okay guys. Now see without doing any changes if I execute once again this insert command right now will it be insert the row into the table in the sense the sense 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 the same role number 101 already with the 101 role number one student is there Once again I'm inserting here. So it should again I'm inserting here. So it should not be see there should come an error. 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 101. 1062 duplicate entry 101. What it is that duplicate entry 101. It is saying that one to you already. 101 student role number is already. 101 student role number is there okay it's not alter hashadan it's a changing of value only alter is a different I'm talking about the data not the column okay so now here I'm adding another one okay so now here I'm adding another one changing the value here you know 1,2 constraint uh here let's let me change the name Now it is morally and it is a male person but accidentally I have given N but we have given check constraint either M or F. Okay. So select this and go for execute once again. See there row is not once again got an error that what is that? See there check constraint. Check constraint is violated because you have given in the place of M accidentally N. So not accepting that is okay. So that m has to be given. Then one more the age should be in between 20 to 30 years. I'm giving 34. Let's see that 34 will be accepted or not. we got at that. See check two constraint is violated. Student chk.2 two is violated. Yes, 34 but it should be in between 20 to 30 only. So that now here you can even if you go for 14 or below 20 also it won't accept 20 also it won't accept okay that is so I'm giving 21 now the row will be accepted yes a row has been inserted see there 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 you define a table with the constraint S while entering data while inserting rows that proper data should be maintained here. If any uh like data is invalid or the row will not be inserted that you need to check. So now with the insert command you check yourself that with the insert command you check that is okay. So that that is the importance of 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 then hower. Yeah, no worries. That suppose that means suppose another person having another a different location right you are asking Mora 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 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 the location. Okay. And here you add the location. Then what happens here? uh the location will be automatically comes to the Delhi by replacing of that hydraad. Let's go for here now. 1003. Okay. Here Navia is the name. Navia is the name. Okay. Female. Okay. 21. Let it be that. Okay. Female. Okay. 21. Let it be that. Okay. Here. So now you go for here. 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 student will come here that is yeah see that third student del has came but in the place of delhi like delhi some other location in the sense you have to put a comma and you have to add as a string that is so So I hope that it is cleared with everyone. 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 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 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 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 with that commands Here we'll continue those commands. So open your MySQL workbench on local machine and you create one database how we have done yesterday in the same way you create one database. You create one database here that is so now here I'm creating one database create 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 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 administration and schemas is there in the schema section go with the refresh the schema section go with the refresh you will be having that Feb now. Yes, you will be having that Feb now. Yes, Feb 2026 DB is there here. So now go with what like use command to access into So this is the one thing just we took now tables are there because it's a local one. Okay, it's a fresh database. No tables are there. Now we will create the tables. We will create the tables. So now I'm going to uh discuss some other commands. So So already discussed other commands. So So already discussed commands is what that ced discussed commands is what that so create database has been done. Then changing database has been done. Okay. changing database has been done. Okay. Then uh creating table has been done. 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 please let me know any queries on behalf of these all things. 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 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. 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 command is very big command guys. So there we have to uh what do you say that is lot of clauses and all that is that's why I kept into separately 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 Huh? what is an update command use? Yesterday we have seen insert command. Yesterday we have seen insert command that insert command having their row adding the new command having their row adding the new rows into the table. So at the same time rows into the table. So at the same time whenever you want to modify the row whenever you want to modify the row values modify row values or change the row values then how we can do that in the sense here the update command can be used here. Okay that is let's see guys here. So what I'm writing now this one this command is used to this command is used command is used to this command is used to change row values to change row values or also allows to perform calculations allows to perform calculations on specific specific uh columns 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 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 and set here column name has to be taken equal to then here value or expression. expression nothing but formula and if required we have to discuss here now some clause called where clause yes this is what the simple command we are having simple command we are having here this is one thing we go is one thing we go so to work on this to work on this one 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 the table, we add some sample data. That means we insert some rows over there. After that, we will start updating the applying the update command on that. Okay, that we do here. That is one thing. So, so here let's see that 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. The table is right now marks table I'm creating. So create table marks marks is the table name. As usual guys I'm giving role number enter and guys I'm giving role number enter and I'm applying primary key. So no duplicates no null values. Then student name I'm taking yes s name is the column name. Then I'm taking workar of sum 20. Okay that is one thing. then exam I'm taking and work 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 it also should not be null like this it 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 column names like subject one only it is 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. It should not accept above 100. zero. 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 less than or equals to 100. check constraint I'm applying the same thing constraint I'm applying the same thing you go for with the subject to I'm not writing my subject names like English math science or so something like that I'm just using that is then I'm giving total marks take integer then I'm giving total marks take integer then I'm giving percentage avr it's a decimal float and I'm giving here result it's a car of Four. What is this result it's a car of Four. What is this result in the sensema? Pass fail. P A S S four letters. F A E A L fail four letters. So car can use because we are using full car can use because we are using full length of string. So that car is okay. So this is guys I hope that everyone understood about that query. understood about that query. 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 refreshma mark here. Then go here that is see 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. 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 insertion. So while inserting the rows avoid this total, average and result because this total should be calculate and average also should be calculate and result also also should be calculate and result also should be calculate. Okay. Pass fail that is. So that how that uh process can take how that process can take here. Yes. First of all insert the rows. So yesterday I shown you how to insert multiple rows. Ma insert into marks. List out the column names which you wanted to add rows here. Okay. Then subject one. Then subject two up to only. Then you go for that values. So one add one. So here uh sur is the name. Then here quarterly is the exam. Then something like 67 and 89. Okay. I'm placing comma. Let me add five rows here. So exam is seeing. I'm changing the marks here. Now 27 it is 59 and 77 59 59 59 or also 49 and this is okay here 29 okay 90 and it is 80 yes guys see guys here like we 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. So that is one thing we are getting. Let's see the data. Select start from Let's see the data. Select start from whatma that is marks can do it. Now whatma that is marks can do it. Now see there here yes we have some five students data is there. See total column average column and result column is empty because we haven't passed the values for that because we have to calculate that. we have to go for calculating that is. So I hope that everyone understood up to this this commands all everything we did yesterday only. Okay. Now create table date inserted data that is now come down to working on this. So update command what I did that my 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 has to fill with the total marks by adding of subject one marks and subject two marks. It has to go for subject one plus subject two. Subject one plus subject two. See that guys I'm writing marks set to which column need to update right set to which column need to update right now total. total is equal to subject one now total. total is equal to subject one plus subject two. That's it. Okay, that goes to that subject one column and subject two column. It respective row values will fetch and that updates onto the total column the total column all the rows not only five 500 5,000 it doesn't matter it will be updates over there. So select that and go for execute here. See there guys, five rows are affected. affected. Five rows are affected that. 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? Total is what here? It's a column name of table. See it's a column name set refers to particular column for that when I'm doing some expression here calculation the result will be updates onto the total column that is what the set see we I got right now five rows are affected now check here now by going to select start from marks now see there now we got a total marks are here are here guys 156 86 26 126 96 170 something got guys 156 86 26 126 96 170 something got it or not guys. Is this quiry is understood by everyone? Is that update query is understood by everyone guys? 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 two by two make it that is a 2.0 or something that okay like this we are taking see again five rows are affected. So check now here affecting rows that see we got the total marks sorry average marks we got it. So you may ask a question here sir why you put here now 2.0 why can't you go for two actually one thing guys uh here data type requirement things will be fx here now so subject one data type is int. Subject two data type is also int. Subjected to data type type is also int. Subjected to data type is also int. If I go with like this two is also int. If I go with like this two is also int. Two is also int. Okay. is also int. Two is also int. Okay. So integer value with any arithmetic operation of another integer will give the integer only. For example, if the average goes here like 57.5 something has came that 0.5 will not be written here. Now if it is goes like this and but if it is going for 2.0 this is part this is everything integer and this is the float. So integer with the float integer with float you do any operation the return value will be the float only but here we haven't get any opportunity to get the decimals. See that 56x2 in the sense 78 only 86x2 only 43 exact values are getting 126x2 in the sense 63 same thing is there okay but if you have such kind of values that also will come this is there now but that is okay that means so these two update commands guys listen these two update commands so updating all the rows of the particular column all all the rows. But right now see that guys here one But right now see that guys here one point. What is that? You know result point. What is that? You know result uh should [snorts] be pass if every subject if every subject having a greater than or equal to 35 marks. This is the condition has been given. See result column is there. In this result column pass should be filled. Pass pass when if every subject having above or equal to 35 marks. Okay. So that means 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 this way. Try to understand now. Update marks set result equal to no marks set result equal to no calculations directly value pass. Suppose if I execute this one in entire result column will get pass. Enter result column will get pass that but it should not come like that. Okay. If every subject if every subject marks having 35 or above 35. But you see here now the first first student sur 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. the pass. That should go for empty only. Then 7749. Okay. Filled. Then 6729. No. Second sub is fail marks is there. No pass there. 80 and 90. Okay. 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 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 Okay. So note you can take down here where clause is used to specify what method is specify conditions how it is let's see now you have to how it is let's see now you have to write like this where see that where so what the condition my here every subject should have 35 or 35 mark 30 above 35 what are the column names subject column Means here subject one yes 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. 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 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 another condition and in the sense all conditions must be satisfied all all conditions must be satisfied should go like that that's the query now let's see we have a five rows okay only three rows has to get update see there now wherever the conditions are satisfied see three rows are affected is You will check that here guys. You see there here and go for that is see there first one got pass third one has got pass second one four fifth one has got pass. See in second row first subject is fail See in second row first subject is fail marks 27. In fourth row second subject has got fail marks 29. But this is also has to filled with fail. No that also filled with one value. 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 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 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 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 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 uh table has been filled here. See there. Now entire table has been filled over there. filled over there. Uh yes yes I think so you all getting an error for update command right? Yes. Yes. Is that update command is like safe Yes. Is that update command is like safe mode something like that is giving? Yes. Yes, it's an update command. Is that yeah fine no worries before executing the update command guys. So just one second because for security constraints so update and delete command directly won't work. So for that one command has to be used here. I forgot that So one simple command is there just wait. Yeah. update command. This command you execute first. Write on set save updates equal to one. You just execute then afterwards. So go for executing the update commands. You will uh it will execute for you. This is the temporary solution. permanent solutions I'll give you first you do this one first you do this is because of avoiding unnecessary updations and deletions directly it won't allow you to update the rows one in the sense enabling updates zero in the sense disabling updates that is but guys one thing you have to go here let's See for example let's say in this table in this table here if if I updated the this table here if if I updated the marks for example this marks not 27 it's like for example it's say like 77 or 47 or something then automatically uh this total average result should get reflected but here automatic updations are not possible here automatic updations are not possible. Remember that. Okay. Here automatic updations are not performed in MySQL. Remember that. For example, see, so how can I modify this marks? For example, so I'll show you now. How can I modify only this marks? Can I get like this mark this marks? Can I get like this mark here? Now let's see. Update marks set of 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 change the you need to go for a condition where condition where role number is equal to 1 or two. Will it work or not? See there whoever having a sharing sorry this updating now see there I got updated that one go with the there I got updated that one go with the select statement guys see there yes it is updated but still the marks are same so total marks are same average marks are same and fail result also same it is not modified. So then what you need to do you have to go for executing all these queries once again all queries has these queries once again all queries has to go for that is okay. 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. 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 all here. H see there 475. So there six it is a 53% pass we got it 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 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 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 105 that is for that question what is the answer I that question what is the answer I wanted to change name as a chendra babu whose role number having5 that means so this nares name I wanted to change as a chendra babu how that can be done given answer. And what about others guys? Please. into the double quotations. Navin Navindra 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 Chundra also it should be quotes. For Chundra also it should be single quotes not the double quotes. received is correct. The little correction on namin raw quotes and for number no need to have any single quotes. that. See there already given that uh some of the learners. 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 5 you where role number is equal to 5 you should get like that execute it that's about update command guys so how here. How the updations will be done. That is one thing we are having. I'm moving on to some other command guys here. So what is another command in the sense? So here delete is the command. Delete command is there. This command is used to delete This command is used to delete rows from table. Delete rows from table. Whenever you want to remove the rows, not the columns, remove the rows. Removing the rows, then we go with work. We can work with the delete command here. So now the command syntax will come like this. Delete from table name. Okay, this is if required a where clause also where clause this is the command we have where clause this is the command we have to use. like this. But of course m just see there. 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 suppose if you execute this one. If you execute this it will delete all rows from table. All rows empty table will give you 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 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 sensma here? It will delete. the sensma here? It will delete. It will delete all rows whose result is fail. Whoever failed those all students will be deleted. We have only one failed student is there. No, but 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. 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 do it that right now there see that location details is not there. See 1 3 1 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 that is delete command is there here simple delete command is there that's it delete command if anybody wants to check that you go with this wants to check that you go with this command I'm giving delete command here yes ma Try to execute that command. 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 forma 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 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 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 wanted to delete existing column row deleting in the sense delete command is there rows 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 rename the column column name I wanted to change column heading column name I wanted to change like We have so much of things are there. Let me write on that. So here, so this commander is used to perform so this commander 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. 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 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. 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 here. Third one here like modify data type of the columns. Okay that is one thing. Fourth one, adding a primary key to the existing columns. Existing columns. That is one thing. 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 want, we can drop that. Then the sixth one here renaming one here renaming renaming existing column names. existing columns. These are the different six operations we can perform different six operations we can perform by using of alter statement. Yes, to do this workma 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 it here. Okay. So that is because to implement these all while creating table we we will made it some mistakes. Okay, that how can we correct it? Because of that how can we correct it? Because of that I'm creating a one more table here. Let's go for here now. Alter no here. Yes, alter command. So Alter no here. Yes, alter command. So now create a table. Okay, now sample is now create a table. Okay, now sample is the table name. Let's see that s the table name. Let's see that s it is in and I'm giving a not null. I'm not giving wantedly primary key. Okay, not null later we add that then yes name where care of some 20 where care of some 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 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. So now whenever we go for guys here you just see there that yes sir 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 here there is a command called describe is there. There is a command called describe is there. What is the describe command? You know that [clears throat] describe command. No no no no. No no no no. It allows to show structure of table. Structure of table that. So how this can be taken up in the sense very simple command it is. So describe table name table name we'll go for it. This is in this describe also this ri i is an optional d also works here. Let's see now there we created a table node. So d e s c ri i b e so sample and if you go and execute this guys you and if you go and execute this guys you see there see it shows the structure. Structure means what? Column name, data type, any constants you have applied those all will come into the here. Now see there now field type not null or null can any keys are there primary key foreign key something have you set any there for any column default that will be shows here something will come that you want to see that see there describe even DC also works my here marks for any 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 that so not null not null has been given so no no null remaining all or yes has been given that is like this you can see what we can call my structure of table what we can call my structure of table okay fine now we are focusing on this now let's see one by one I'm taking meth add a new columns add a new columns add new columns so by name phone number add new columns so by name phone number what method is phone number and pin code let's see that these two columns I wanted to add to which table to the sample table. These all are coming working with the sample table only. So let's see that how we write that alter table sample see add first phone number I'm taking only phone work of 410 yes one column I'm adding here just see there now simple there is no different action here now after add you have to keyword the column name new column name and it data type that's and it's data type we have to go for and it's data type we have to go for like that only simple command it is yes ma'am so column has been added that but how can I see that you have to check but how can I see that you have to check with the describe only with the describe only see own has been added here now you want one more column adding same command one more time you repeat it so here uh what is that pin code pin code is Sixdigit spin code. Yes, six. I'm adding that also. Yes, you see there that here. Yes, pin code like see there the columns are extending here. Now 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 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 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 have written here of expression in the syntax because every aspect is going to syntax because every aspect is going to be different writing of command be different writing of command drop column column name phone column I don't want yes it has been executed ma go for once it has been executed ma go for once again describe here see no phone is there that means no phone column is there that has been removed that has been removed that okay so that that has been removed that okay so that is one thing here we are checking then one more so here you see that whenever you scroll down I want to whenever you scroll down I want to change the data type of this same 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 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 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 column what is the column name yes name. Okay. What is a new data type? You just give it where car sum 20 then not null. Something like this. That's it. Here it is null constraint is there. I added the not null constraint. Select this and execute. Now this one. Yes, it executed successfully. That is right now there. Go for executing that 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 the null become as not aware 20 and the null become as not null. No. 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 the primary key here. So add one more that is primary key constraint. that is primary key constraint. Add primary key constraint to to SNO column. Then what should we write here? Uh yes 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. primary key? Yes name column. Sorry. Yes. So that you just see this one here. Yes, it has been executed. 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 we are It is primary key. That one part we are having here. existed if I'm doing anything modification that not that one it is only add 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 I don't want primary key. I don't want primary key in the sense. So, actually the primary key have given to the wrong column. So, remove that primary key to the existing column. Then add primary key to any other column which is suitable one. Such kind of things. If you want to do how to remove the primary key here in the sense just see that delete primary key. Delete primary key. Simple thing that how to delete a primary key here. Alter table sample sample drop what drop simply going for that primary key you no need to specify the column name because as I already 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 there primary key to which column it is there for that it will be deleted Okay. So, how can we see that whether it is deleted or not? is deleted or not? See there no P here. Previously it was P See there no P here. Previously it was P that is primary key. Now no primary key that is primary key. Now no primary key like this. So we are having this one. This is the one just we go for it right now. And finally we go for like this. What is that? Rename a column that? Rename a column from s name to std name. from s name to std name. Rename a column from s name to std name. S name column want to change the std name. So then how can we go for that is alter table sample rename. So that I would have told you that yes name name into std name I think. So that is what happened to this is rename column. Yes. Name old column name. Uh yes. see this one here. So now the column name becomes here. Now std name. See name becomes here. Now std name. See there. 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 there are two commands is there. The very first command is right now drop. very first command is right now drop. Drop command is there. Now okay. What is the drop command here? The drop command goes like this. Uh this drop command goes like this. Uh this command is used to delete any object 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 objects are there. Objects in the sense you will come to know in the next coming sessions. See there here if you're taking on that tables is there then views will work then stored procedures will work then functions will work to delete any of these things so then in the middle of that we'll be having some indexes will come there then triggers will come there these all has to work on here the next coming sessions okay so these all and that to even database 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 So we have to write down the syntax like this. Drop object. this. Drop object. Okay. Then here object name. Object name that suppose to delete a table. To delete table what should we write? Drop table name 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 name database 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 drop index index name. Okay, like this it goes here. And now uh earlier to this so we discussed about delete command. Earlier to this we discussed about delete command. What makes the difference between a delete and a drop in the sense delete will delete the only rows but table will be existed. You delete all the rows but table is existed but drop is not like that. Once uh you use the drop command entire table will be the drop command entire table will be deleted. Entire table will be deleted here. Including data. Table deletion including data it will be Table deletion including data it will be deleted. That is one thing. And one more point here. So whenever you delete some rows in a table. Whenever we delete some rows in a table. What must sir? How to get started with MySQL workbench in case practice lab not working? Okay prai just I'll take to your screen just wait for a five 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 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 that we cannot get back here so that drop command will delete the so that drop command will delete the table permanently. You cannot revert it. Whereas if you delete the rows from the table, you delete the rows from table. So there is a chance to reverted that. There is a chance to revert it that There is a chance to revert it that here. Okay, that is one thing. There's a simple command drop. There is no any wear clauses or something is there here. I'm not deleting any dropping any table right now. Of course, if you want you can drop table here. You see there the sample table will drop it here. sample table will drop it here. You just go for drop table sample. 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. Okay. Now truncate. There is a command called truncate is there. there. Truncate command this is this command Truncate command this is this command is used to delete all rows from table. is used to delete all rows from table. All rows from table. So that means here what is that trate? This one you go for that truncate table table name like this you should get that 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 and truncate? So delete and truncate. The difference between delete and truncate. Delete and truncate in the sensema. Let's see that delete allows to delete allows to delete specific rows based on condition. 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 whereas truncate truncate deletes all rows. That is the first point. Okay. Point number one. Second point. 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 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 okay and whereas truncate always going for all rows deletion. Truncate is going for all rows deletion that is and you have a chance to get back the deleted rows if any important rows has been deleted with the help of roll back command. But in a truncate it is not permanently rows will be deleted. If you delete all rows from table with the help of truncate command truncate command that rows you cannot get back that rows you cannot get back here. That is the point you have to be remembered. That's about truncate one that is one here what truncate command is there here you no need to practice these things my here but because the data what it is there I required that for next coming examples that is so here for next coming 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 complete completed right no need to practice drop and truncate just remember that is whenever it is required then you that is whenever it is required then you can go hope that the difference between a drop delete to truncate understood or not drop command will delete entire table but other objects 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 of rows based on the condition and you have a chance to get back those rows. We noted to discuss in the roll back. We'll discuss just wait for navindra. Okay. commit to roll back commands has to discuss combinationally just wait for 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 some other command called selectma so because select command how to retrieve data from table and all that we'll go for it right now let's see that is how for it right now let's see that is how it works okay the next command is guys here select command has to go for it here now what is that select command guys first of all one thing uh just to discuss about this select command we need proper data we need proper data that is so as I said beginning of today's session okay everyone has been downloaded that okay everyone has been downloaded that SQL data sets right downloaded. No, SQL data sets right downloaded. No, got it. In this SQL data sets, in this SQL data sets, ma go to the assisted projects and so check that this EMP table is there or not here. It will be there. M that see there when you open SQL data sets, assisted practice data sets are there. There you have lesson five, first lesson and you have here EMP table is there. It is the it is not Excel actually it is CSV file. When you double click on it when you double click on it you see there here you can find some sample data. some sample data. Now this data we wanted to bring it on Now this data we wanted to bring it on to our database. Okay, this is now this data we want it right now. So what I'm doing right now you know here uh what you say that is so this is what uh what you say that is so this is what type of file in the sensma it is CSV file you just see there that is so see there Microsoft Excel comma separated values file that is CSV but it supports Excel so that we are able to open on Excel that now this table is there some data that data we table is there some data that data we wanted to importing into our database wanted to importing into our database from CSV file to here from CSV file to here we wanted to import that import that so now I'm performing one small task just follow everyone and try to do it that you have to guys 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 option called table data import wizard. Table data import vizard is there. You click on this. click on this. So you'll get here now go to the browser 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 and SQL data 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 yes you'll get like this yes you'll get like this then go to the next so here it will ask you that so table name what is the table name Just keep it the table name as EMP only. The file name will be taken here. But you go to name will be taken here. But you go to EMP that is remove the underscore table. EMP that is remove the underscore table. Just keep the emp columns and its data. Everything it is showing to you here. Now the data is coming right now there. Then next. next. Okay. Simply go for next and next. Next. Go for finish. That's it. Ma the table will have here. Just see that. Go for refresh. See you have a EMP table. have a EMP table. You have a EMP table that separate file 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. 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? guys? Do you want me to show once again or else will you do it that 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 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 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 because of that I'm not continuing that yeah so I hope that everyone has imported that EMP table, right? Everyone imported EMP table from CSV file to your database. 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 has different clauses and all that is there. Let's go for one by one here. So [snorts] how we can go for that is so first of all the purpose of select command is here to retrieve data from single or multiple tables. We already working on uh previously also. Let me select start from table name or something that the completely how you get that is you see there here. See there 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 discuss now. So select and here expression should be there is some expression you'll come to know that from table name that is this is mandatory part and the clauses what we have here these 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 have taken one table that is employee table. table. In this employee table guys you see the data. So first of all we'll talk about this employee table. Have you observed this employee table. Have you observed that table data here employee ID is 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, employee rating, manager salary column, employee rating, manager ID like you are having. 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 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 this 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. 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 Janet Halle female can she's a manager. Manager for what? Retail department. See so Joy Collins is an employee of senior sat scientist working in retail department for the department Janet H is the manager okay 14 years of experience living in Colombia South America taking 10,000 Colombia South America taking 10,000 salary okay now employee rating is two but she is the manager but she is also having next reporting manager who is that E002 who is that E002 to you have to search who is that E002 to you have to search that E002 here. Yes. Cynthia Brooks 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 report to someone who is that E001. Who is E001? is E001? Arther black male CEO of the company. Okay. 20 years of experience USA and North America. So CEO is not going to report to 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 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 this data we'll make some queries as of now. So select star from EM. Can you tell anyone that what is meant by star here? What is star represents? Ma what the star represents? Somebody having some already knowledge. No for those people I'm asking that. No for those people I'm asking that. H it's not a complete data all columns H it's not a complete data all columns are correct. All columns 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 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 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 define the column names that's it define the column names over there so how you go for that is your select So I want first name so that first underscore name then comma so then job is there ro then dt okay then salary so from emp those four columns I want only those four columns data we can get it that see table having all the columns but right now only visibility is four columns right now visibility is what mother Four columns visibility we are having that is. So that is one thing just we are getting right now like any number of columns any sorry any columns which you can take sorry any columns which you can take down that that is not an issue here see all table that is now here we can perform some calculations over there 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 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 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 so first underscore name I'm taking only first name that so then ro then salary so that is fine here then here you can so that is fine here then here you can write like this salary into 12 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 7,000 is the salary monthly salary that is and salary into 12 in the sense 84,000 is there now like you have now all the employees salaries that is monthly salary and yearly salary is there but this column is not present remember that this last one is not will be present on the table no for temporarily selected statement is generated that column for showing the result that's it we're showing the result here but in a table only you have a salary column no other column will exist there here except the physical all columns that okay that is one thing but here one point is there now 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 But I don't want 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 that we are having here column aliases that we are having here column aliases what is that column aliases is there what is that column aliases is there okay say this is what column alias so it it is an alternative name for the table columns are expressions of select 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 specify it alias name has to specified that is see how you write a query for that mark see there now I'm taking right now there selector first name as a emp name see I'm first name as a emp name see I'm changing the name of that ro as So designation then salary as a monthly sal. So then salary into 12 as a yearly sal then here salary by 30 as of day wise sal okay from emp I'm writing for each column I have given alias name first name I took as a emp name role has been taken as a monthly physical column names remaining two are 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 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 a table physically ally 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 the headings we need that we are getting that's called as an alias column aliases what is that column aliases you can write on here so this is one thing so guys here first what we taking getting all columns listen 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 it like this this is what along with the physical columns then by using column aliases for the calculations if you want headings or something then you can go for here that is first name as emp name role as a designation salary as a monthly sal something like this. 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 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. 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 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 [clears throat] now. Okay. Senior, junior, associate. Let's see some other one here. Lead data scientist four. Lead data scientist four. Manager is five. President is six. CO7 like you have that and even if you come to the department column you have a deep uh retail department one department finance is another department automotive is another department healthcare is another yes that's it four departments are there all five departments and even when come to the here now 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 female. I wanted to see that is 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 output of this query? Ma select DP from EMP. In the sense what is select DP from EMP. In the sense what is the output of this query? What is the output of this query? All values of that dpp column will come. All values of that dpp column will come. Right? Only one column d will come. All Right? Only one column d will come. All 20 values will come. Right? All 20 values will come there. See there. Now, let me execute that. is okay this is one but I don't want that okay this is one but I don't want that duplicates I want actual values very duplicates I want actual values very simple thing that just add a distinct simple thing that just add a distinct dB from emp now what will happen you know only actual values will come see it eliminates all the uh duplicates and shows actual values only for showing prospection in a table. It is there we are not disturbing anything table only retrieval prospection. See there now five departments we have like you have other also suppose like a distinct gender group in the sense what chendra I'm not getting your point I'm not getting your point gender spelling mistake 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 like this. But that query is correct. That query is correct that. So then here now country So then here now country from EM actual countries will be showing from EM actual countries will be showing to us here. Now see like you can take down guys. I hope this distant clause is guys. I hope this distant clause is understood by everyone. Right? understood by everyone. Right? Right now is it clear or not? like you have some guys here just let me continue the explaining this anyhow you have a table ready so now this queries you can 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 statement what is that section here select cl this section is complete completed. So in this expression area what? In this expression area we got three sections there. One is all columns or specific columns that is the first one. All columns in the sense star specific columns in the sense taking only that column names. The second one is performing calculations. While doing calculations if you want we can give aliyas 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 we should go. 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 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 clause. So it is used what method is used to specify conditions on table column to retrieve rows to retrieve rows. That is how it should go. Select blah blah that is one comma here where column name operator value here where column name operator value has to specify. 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 working in uh what is that uh India country guys. One second, guys. 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 column is there in this country column we go for like this where country is equal to India has to single quotation is must because of stringle see there where class only three see there where class only three employees are working in India three employees are working in India that is okay now you tell me that show all male employees show all male employees male employees show all male employees give me the query for This show all male employees. Give me the query guys. the answer are given that is correct there 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 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 requiry is correct but no value male requiry is correct but no value male okay M only like that any conditions you can write on here. Suppose show all employees in uh like show all managers. Show all managers. This is one more you can take down. Okay. Then show all uh can take down. Okay. Then show all uh like employees. So in uh what we can call that retail department retail department that is one more okay this is one thing these are the simple conditions that is guys here now then conditions that is guys here now then show all employees so whose experience is so greater than or equal to 8 whose experience is greater than or equal to date like this. Now see there now we have given conditions on only one column. All these queries are having one column conditions. Suppose if you have the multiple column conditions then how the multiple column conditions then how can we go for that is let's say now here can we go for that is let's say now here uh show all employees whose sal is greater than or equals to 8,000 and less than or equal to 10,000. So I want to go for like this. So one here see the query can get like this here. Now select start from EMP where see that guys salary greater than or equal to 8,000 and salary less than or equal to 10,000. You must write like this only. Okay, this is let's see how many employees are there whose salary between 8 to 10,000. Yes, we have a like five to five employees are there here. Let's see 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 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 we can write using between how you know that select start from EM how you know that select start from EM where salary between where salary between 8,000 and 10,000 this is also give the 8,000 and 10,000 this is also give the same answer see there now this is also same answer see there now this is also gives you the same answer that see Same answer has been given to us using between here. Now anyway we can use but the second one will give concise the query. 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. 85 is there. Of course 8,000 salary no employees are there. Okay. You do one 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. and one more is there here. Let's see that guys. that guys. Uh yes, one more query here. Show all employees uh in uh retail in uh retail and automotive what automotive departments from those two departments we need to 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 So then we can go for here now where DBT is equals to retail is equals to retail or DBT is equal to automotive. You need to write like this here again one column with R operator. So that is and in the same in same column in same here both retail and automotive it will not be there. Either it should have automotive or else it should have retail or else it should have healthcare like that I'm getting have healthcare like that I'm getting two you see there here select that and go for executing that is right now 11 employees are there in from those two departments 11 employees are there here to explain here if you keep the under see what happens There see what happens There no rows will come 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 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 or else automotive any one condition is true. true. You got my point? Winkut. 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 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. will be getting. See retail and automotive employees are there. column because condition is on department column. No, there it checks department column. No, there it checks here is so this is the one we are have. Okay. So now we'll go for right now multiple conditions on multiple columns. Let's see that multiple conditions on multiple columns. For example guys, you see here show all male managers. See if I ask like this a question. Show all male managers. I want all managers 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 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 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 what that is petty Alen and Patrick Wols is there. Now only two employees are there. Two manager male managers are there. See guys try to understand salary column between operator two conditions department column or operator that is in that means previous two examples multiple conditions on same column only we did right now multiple conditions on different columns. Now one condition on gender, one condition on male. So that is one thing we are having here. So that is one thing we are having here. Okay. So then one more query suppose Okay. So then one more query suppose here see show all female here see show all female employees. So all female employees 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 this? this? Will you write the answer for this guys? I will write the answer for this one first. Will you try for that? M gender equal to MF and salary between 5,000 and 10,000. and salary between 5,000 and 10,000. Mhm. Yes, ma gender of and salary. So, answers are all answers are correct only that I'm receiving see there now here gender is f4 and we can write on 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 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 it right now there yeah shall we go for one more here see show all employees. So whose department employees. So whose department either what is retail either what is retail or automotive salaries between uh what that is 5k to 10k. between uh what that is 5k to 10k. Now can you make the query for this? Can you make the query for this? here that values you can replace it to either any other values like 5k to 10k 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 so this is the correct answer I'm 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 or automotive. Okay. And salary or my salary between 5,000 to that means under 10,000 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. 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 years. Now write the query for this. I'm seeing the data and writing that questions in front of you that to make questions in front of you that to make you practice. 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 no so then five years show only first name only first name and uh experience column and experience columns then if I write like this what is the 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 yes in the place of star you'll get that is is right 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 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. senior data scientists what mother is senior data scientist so any spelling method is scientist from any spelling method is scientist from retail department uh I don't know now. retail department uh I don't know now. Show all senior data scientist. Can I hear that is use only first name first name, uh what that is job and department columns. Yeah, department job in the sense here it is ro the visibility only those three columns here 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. getting any right issues while writing the queries, please you know write a query here. I'll help you. 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. See there I have written over there. queries here now. That means you got some exposure writing the queries and multiple conditions and all that is it still we have some more questions are there but okay let me go for other topic. topic. So in this wear clause only we have now 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 operator is there. What is that like operator is there. What is that like operator? How to add names of manager in the row? names of manager in the row? Next to ids right you're asking about employee ids chendra chendra so that means employee ID and uh who is whose manager right who is whose manager that is Yeah, for that we have to write a self join. Self join has to be right. That is joins concept. I'll give the answer for this. Who is whose manager? That one. Okay. Self join query is there. Now I'll show you there. called what is that? You know that is a like operator is there here. like operator. like operator. So this is used to specify So this is used to specify condition pattern based conditions 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 thing. So here two uh symbols So here two uh symbols has to be used. What is that? You know has to be used. What is that? You know the very first one is like percentage. the very first one is like percentage. So it ignores group of characters. So it ignores group of characters. Group of characters that then another one is underscore. Okay. Ignores Okay. Ignores single character. single character like 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 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 Remaining all anything remaining all anything here I want to go for like that then how you write that is how you write that one here so for this kind of situation sir you are having a like this what is that you know like select something like I'm writing that where first name mean 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 are anything. So that should be ignore. Ignoring of remaining characters. So that right now the percentage symbol can use it here. use it here. Percentage symbol can use it. Okay. Let's see that any employees are there here with the starts with yes. Yes, only one employee is there. See there Steve. Steve is there. Let me check first of all list of employees. So you will get some couple of data over there. Yeah. of data over there. Yeah. Uh so this query you make it with K. 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 Karina is there. Now see in the first name only see in the first name. So like this sir if we put here equal to what happened? Equal to in the sense it will search for an employee with K percentage. An employee with K percentage employee name with K percentage. It's not like that in employee in a employee first name first letter should be K remaining all anything that grouping will be taken 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 that is ends with for example with what that is ends with for example let's say that some a last letter is a let's say that some a last letter is a so percentage a should write here now 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 there three employees are there. All the employees first letter is A. employees first letter is A. All the employees first last letter is a the something we get that is and one more also you can go with that uh what is that show all employees whose uh first name first name exist what mother is exist alphabet a exist what mother is exist alphabet a exist exist in the sense now anywhere exist in the sense either in first position in last position the middle anywhere if you gone on that then percentage a percentage will go. Okay, that is then go for clicking on this one. Almost 13 employees are there. 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, Karan, Williams, Neil is there. Katran, Karan, Williams, David, Diana, Ched, see Clary, Patrick. See everywhere you have alphabet A. See Arthur first letter is A. Cynthia last [clears throat] all having some in the middle. 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 use that underscore when we use that uh underscore symbol the underscore symbol will be used like this uh underscore in the sense what ignoring single character ignoring what that is single character suppose same thing that let's see that is here I'm writing here now show all employees who first names second letter starts with let's say that Second letter starts with a for example let's say that second letter that means let's say that 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 percentage so first letter is one underscore one letter then a then remaining all letter then a then remaining all characters should ignore that you can take down Here see there stick the second letter of every stick the second letter of every employee here. So Janet, Patrick, David, Karani, Katrina all second letters are having a suppose if you want the third letter is a two underscores 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. 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 5 underscores David five letters Janet five letters Emily five letters Tracy five letters one more I'm just giving that uh like first letter I made it like here uh like first letter I made it like here now uh yes see first letter is yes sir and remaining four letters I have given ignoring yes sir 1 2 3 4 Now tell me ignoring yes sir 1 2 3 4 Now tell me what is the result of this query anything. First letter is yes is okay. So we used underscore over there that means total length of the name is five letter address within that first letter should be yes only that is the meaning okay that means so you'll get only ste with this time ste only you'll get but the question goes like this here now so length should be five characters and [snorts] uh name should starts with name should starts with what mother is alphabet. Yes, you should go for like this. 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 percentage and underscores. How can we use it? is all here use it? is 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 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 just write on that here so show all employees of whose name. Whose first name? Whose first name? Second letter Whose first name? Second letter is A and fourth letter is A and fourth letter is is again something E. Let's assume like that. anything. Uh E. Fourth letter is anything. rest of let us let us ignore it. Now write the query for this. What should how to select employees first which has a employees which has a as the last character in select like underscore a andore e percentage is Chandra Praash you are missing that uh underscore for the first not percentage no how you go for percentage underscore should go swagadika your query will check only four letters four letters which is having second letter E and fourth letter E. You should letter E and fourth letter E. You should keep the percentage symbol after E A_E then percentage should I don't know how many employees are will come yeah we have a two K and Janet 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. You have to see that two lines of question rest of letters ignore that is what actual question complete question is that is here so that's about some of the examples of where clause so that is what as for the syntax now where clause is completed where clause is completed now combine come to this group by clause but one thing guys whenever you 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 group by clause. Group by clause cannot be used directly here. Group by clause need some predefined functions called group functions. We'll call it as what that is group function should. Okay. So what are that group functions Okay. So what are that group functions and how can we use those group functions yeah proy I'll speak with you at the end of session so these group functions the this these functions are used to perform 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 max min sum average count star something is there. Let's see now how these functions will work first. work first. So whenever you come down here So whenever you come down here so that here now yeah so that here now yeah select I'm writing sorry max of salary from EMP if you go on taking like this guys 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 so let's see that is right now there what that is find max sal now here uh find max comma min comma Sum cell. Okay. Then average sal. Average sal. Okay. from all employees. Then how you go for that is you can go for like this guys to find the maximum for like this guys to find the maximum salary max of sal is there salary then minimum salary in the sense min function is there minimum salary then total all 20 employees how much of salary they are taking there is a salary they are taking there is a function called sum added that salary so then here on an average how much of salary they Every employee have AVG salary salary from EMP. That's it. Here see that the maximum salary is 16,500. 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. taking 7,815 rupees every employee. Okay. So like this we can so with this functions this is only we can do it here. And one more see there here suppose count the suppose count the um count all employees. So in a table um count all employees. So in a table in a table that how you go for that is select count of star count of star from M. So how many rows are there? That is 20 20 rows 20 employees. That is every row having one employee little 20 rows 20 employees. 20 employees. And at the same time if you want you can count number of values in a column. count number of values exist in for example number of values exist in for example let's say that first name column then in this case you have to go for like this counter first name like this counter first name from emp 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 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. 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. note. What that is null values What that is null values will not be counted. will not be counted. So that is one thing you have to. only functions that is okay but one more point you have to remember here what is point you have to remember here what is that you know you may find uh you may get an idea sir it is showing maximum salary okay 16,500 can I get employee name of that maximum 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 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. So what is that error? It is coming there in aggregated query without group by clause cannot be used. That means by clause cannot be used. That means guys here no column should be used directly. No column should be used directly if 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 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 these functions utilize in a different ways in the sense based on these ways in the sense based on these functions we have a clause is there functions we have a clause is there group by clause. group by clause? See that this clause is used to divide a column into group subgroups. subgroups based on other columns based subgroups based on other columns based on other columns then allows you perform on other columns then allows you perform group functions. what is that you know so how the group functions so group by clause can use in functions so group by clause can use in the sense show max salaries see thema the sense show max salaries see thema here now show max salaries here now show max salaries based on each department I write like this so select max of salary from EM in the sense from all the employees maximum salary showing that now I wanted to show. Now I wanted to show maximum salaries on each department. Now I wanted to show maximum salaries on each department. Then how that can be each department. Then how that can be taken up. Yes. See there. Now select max of salary from EMP. [snorts] from EMP. [snorts] So here you have to write down group by So here you have to write down group by what is that? D E P we can use like this see that 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 column name you used 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. 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. Automotive is 11,000. Healthcare 9,500. All is 16,500. like this. What is that? Take this one here. 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, you go for in this way country, then what should we write? India the maximum salary taking is right now 8,500 minimum is 3,000 totally now 8,500 minimum is 3,000 totally 18,500 is this 6,166 is the average salary like we have a chain China. Okay. Now only one employee is there I think. So so that maximum Colombia. Okay. USA that is Germany and France that Canada. so what is that you know counter number of employees in each job role each job role that is count the number of employees in the job role role. role role. Okay. So how that process can take here? from EMP group by what that is group by role can group by what that is group by role can go for it. 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 So this is the one just you can write down with it. Okay. So this is the way we go for working on the group functions and the working on the group functions and the 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 of the time group by clause can take on group functions only on group functions of Chandra Praash. I think so you have given one query like this. What is that given one query like this. What is that gender? Okay. Now from EMP gender? Okay. Now from EMP group by gender see that two groups the distinct clause also we did that. now. Okay, this is group by clause can use 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 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. One more is there. What here? Now having clause is there. clause is there. Okay. Generally what is the purpose of Okay. Generally what is the purpose of uh this one? Where clause? uh this one? Where clause? What is the purpose of wear clause guys? purpose of wear clause? Why we are using wear clause to by specifying conditions we can retrieve specific rows? Yeah. Filtering prospection. Yeah. So now you tell me you are we are making conditions right? We are making conditions on what? Columns. 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 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 group by Only if a group I 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. 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. So what is that one here? Let me execute this query once. extending this question like this those max sals must be must be greater than or equal to 10,000 see there must be greater than or equal to 10,000 those max salaries so that means here This 9,500 should not come. This should be avoided actually not healthy. This one based on this is then here you have to write on having just see that again max of salary see that again max of salary greater than or equal to 10,000. You should write like this. See I'm specifying condition on group function. Group function. This is the condition is happening on function not on the column. happening on function not on the column. 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 without group by. Okay, that is now you see there here see healthcare has been see there here see healthcare has been removed that means filtered that the same thing just see there here uh what is that uh uh number of employees in each Department of whose counter is greater than or equal to counter is greater than or equal to five. Greater than or equals to 5. That five. Greater than or equals to 5. That is then how can we write that having is then how can we write that having count of star greater than or equal to 5 count of star greater than or equal to 5 that you can take down. 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 there 2 4 and 1 is there. Now those all has been reduced here. is also going for four. 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 there here guys are you able to follow or not following or not these queries what I'm writing group by clause having clause writing group by clause having clause group functions Yeah. So this is one setma. This is completely one set here. What is that? Up to where group functions group by clause having clause up to from here. That is one set. First group functions are aggregate functions. Then on top of that group by clause then on on top of that group by clause then on top of that having clause. Okay, this is and if you take some other examples here, you can also include the wear clauses also. You can also include the where clauses You can also include the where clauses also here that is okay. So that for that also here that is okay. So that for that also we can do here that yeah now let's also we can do here that yeah now let's see that one query I'm asking here. Let me show maximum salaries. maximum salaries. Maximum max comma min salaries. Maximum max comma min salaries. What that is mean salaries retail and finance and finance department. I ask like this department. I ask like this retail and finance department. So here you see this how I'm writing that. So I'm including here group by and as well as where clause select whatma that is DB max of salary comma min of salary. comma min of salary. Okay from emp group by d this is done. only retail and finance I don't want remaining all then how you filter that is apply the wear clause if you want to apply the wear clause guys so where clause should come always after 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 where group by having that is like that we will be getting that. So here see we 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 retail comma then what meth that is finance showing an error that see it is not allowing you write the wear clause after the group by so take this so take this after this you I know. Guys are you able to follow or not? Understood this quiry. so I'm taking to you last clause of the select statement ma that is here select statement ma that is here order by clause. Very clause. See there order by clause. This clause is used to show rows either in ascending or descending rows either in ascending or descending order of specific column. This clause is lost clause of select statement. de S is something we need to get that is de S is something we need to get that is that means ma let's see that here order by clause so whenever we go for like this what is that here show all employees So in ascending order of salary ascending order of salary that is then we can go for like this see that select star from EMP for example see right now guys salary column see the salaries are here salary column see the salaries are here randomly there 7,65 3 something blah blah is there but I want First lowest salary then highest salary that then can we can write down like this order by salary. Now you see there how the data will come. come. See first 2,800 and gradually increases that. Okay. And finally you are having a Okay. And finally you are having a 16,500 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 clauses within it then order by clause has to yeah by default it is ascending. So that if other classes are including order by clause has to move to the last order by clause has to move to the last it has to move on to the last it has to move on to the last 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 mother is finance should come but it is not coming no worries take this query here. worries take this query here. Okay. Write now Then what should you write? Ma here you have to write order by DBD. Now see this result mark retail finance. Now finance and retail should come first [clears throat] finance and then retail will come there. Like we did so much of queries here. H take this query. H take this query. Take this query here and apply order by clause. Order by first name D S descending order. If you want descending order, descending order will come here. is on first name I did that. See first it starts with Williams and See first it starts with Williams and last it goes with I. Yeah. Jed to a last it goes with I. Yeah. Jed to a process will come over there. Jed to a process. Okay. So that is one thing just uh we are having. So order by clause is the here the last 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 like this. So in ascending order of the country. then ascending order of the country then ascending order of the country then first name So you just see that select star from EMP EMP order by country first. order by country first. See there now order by country. 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? 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 Diana then Emily it should come like that in that case of comma you have to that in that case of comma you have to write first name first sorting will done on country after that then first name now see this result Diana Ched Emil Cynthia is there 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. 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 is there right now. Three employees are there. This is the Colombia part. Yeah, there. This is the Colombia part. Yeah, Smith. the first letter is same it take the second letter the first letter is same it takes the second letter that remember like that here now in that way it goes here now got it guys understood that is so multiple columns also you can take multiple columns also you can take ordering like this This is about today's topic here. Okay, we covered couple of commands. Still we 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 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 statement is there that is called case statement statement case statement that is one Okay, case statement that is one Okay, after this so we have now there again after this so we have now there again now creating table using relationship primary key foreign key process. The fourth one is here joins fourth one is here joins joins processes one we are having. Okay. joins processes one we are having. Okay. Then the fifth one is right now there uh views. Okay. These are the next week target. Even if the time permits we go for stored procedures, uh indexes and all that is here. These are the next week one here because the these two are big topics. These two are big topics that is but 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 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 can go for one more. Is there like 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 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 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 one query within another query we can call it as a subquery as for that as per call it as a subquery as for that as per that process guys here one select command we are going to write to another select command one select command we are writing on can do some other select commands act. Okay. So that is one thing we can do as of now. Okay. Now let's see that if 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 need to specify fixed values. What that is specify specify fixed values fixed values. So in wear clause conditions what are the in where clause the conditions that this is what has to go some not all the time sometimes we need to specify the fixed values that is compulsory we can't avoid completely but sometimes not every time but sometimes we can avoid and we have to be avoid we we can avoid and we have to be avoid we have to be avoid specifying fixed values have to be avoid specifying fixed values while writing conditions on wear clause. So what are that? What are that one here? I'll show you one thing. Now let's see. So last class we have been discussed about uh like aggregate functions or group functions. Aggregate functions are group functions. See there now here select max of sal max of salary. So from EMP I'm just taking that is an EMP table you are finding the only maximum salary here. Suppose sir if you ask like sir can I 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 maximum salary that employee details I want right now that employee details I want right now there so then how that can be taken up usually what is the maximum salary we got right now 16,500 okay that here now so select the star from EM where So what is that here now? from EM where So what is that here now? Salary is equal to 16,500. Usually we write like this just listen my point. Usually we write like this. So is Arthur block CEO is the role of that person. So 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 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 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 Right? Two steps, two select statements. So can't I get this entire thing in one statement? Whatever it is the maximum Whatever it is the maximum salary doesn't matter. It has to show all the employees who is getting maximum salary. 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 fixed value wherever you are mentioning in that place of by are mentioning 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 means can I write like this here? Now see there select max of salary from EM we will get like this. 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 able to get this one. So let's see that here. Now uh whatma So let's see that here. Now uh whatma that is show who is getting max sal something like that. In the same way see there now show an employee details who is getting min who is getting min lowest sal. So can we go for like this here? Now what is that mean 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 but how just I'm showing the queries but to write this subqueries one here to write the subqueries one we have some rules. Yes, that is simple. See that what are the rules here? See a sub query must the rules here? See a sub query must return see must return single value or return see must return single value or single column values. Single value or single column values. So based on operator used operator used that is the first one which means so that is the first one which means so actually from this one just from this 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 query better you make this one into the top then that is the second point a subquery. So what that is has to be written in var class of main query and written in var class of main query and second one what that is that subquery must return single value or single column values based on operator used. Then here first see that first subquery will be executed will be executed based on the subquery result main query get executes main query get executes here. So that is what just we have to go for now. So which means when it coming to right now there you see this is the subquery. This is the subquery here. This is called subquery and this is called main query. This is called what here? Main query. This is called what here? Main query. Okay. First subquery will get executed. Whatever the subquery you write that query will get executed. You will get an answer. So suppose see I selected this part now then go for selected this part now then go for executing of this see 16,500 is there okay that is one thing then that now select start from emp where salary equal 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 come here now see that 2,800 then main query goes select star from EMP where 800 [clears throat] the minimum salary employee details will come that is okay and that to one thing this subquery and that to one thing this 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 here now you are using here equal to or else less than equal to Or else greater me that guys. If you are using greater than, less than, equals, not equal in the sense. So how many values it will take? take? How many values it will take? Guys for a condition how many values it will take? take? Only one value. Yes, that is okay. One Only one value. Yes, that is okay. One value that one. Suppose in case if I write in operator then how many values it will take? If I write in operator how many values it will take? That is just a multiple values. That is what here now in operator in the sense what here now in operator in the sense multiple values that is okay multiple 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 guess it single column only that point you have to remember okay see where salary in for example okay see where salary in for example [clears throat] like see salary in suppose if I'm writing like this 10,000 okay like 20,000 30,000 if I write like this these all salaries from these all values from the salary column only so values from the salary column only so multiple values of single column that is the point here we are I'm mentioning here now see what that So a subquery must be written single value or single column values based on the operator used the operator what you are using here. Okay. Equal to, not equal, less than or equal to less than, greater than or equal to greater than. If it is like that single value in operator in the that single value in operator in the sense multiple values of single column 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 value or single column values. That's it. That point you keep in mind. Okay, that is my now see there with the subquery sir we got it here this way. So this is one let's have another example of subquery example of subquery can I get like see see this is what here now showing maximum salary it is what that showing maximum salary it is what that showing maximum salary it is suppose if I ask okay show salary it is suppose if I ask okay show second maximum salary I want only second maximum salary not second maximum salary Employee details I want only second maximum salary I want it here then how can we go for that second maximum salary here yeah with the help of subquery I'm solving this see there select max salary solving this see there select max salary from emp it's a very little tricky here salary less than within this again going for less than within this again going for select max of salary select max of salary from EMP that I'm right from EMP that I'm right understood this one properly 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 query ma what is the operator we used here? What less than first this is what you get there. Listen carefully. Select max salary from EMP in the sense. So you get 16,500 only that is subquery is written 16,500. 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 salary from all salaries of salary column by excluding by excluding 16,500 because you used here less than operator. 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 not ma clear or not that query please let me know are you able to follow or not again if you put now less than or equals to again 16,500 will come it it goes to again 16,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 that you have to go for only less than that maximum salary exclude now we are that maximum salary exclude now we are getting this is yeah like this we get and one more that you see this here uh Um what do you say that is? Yeah. So show an employee details second maximum sal now you see there now. see there now. So for this so I'm going to write a subquery within a subquery you can write like that a subquery within a subquery you can write down you can go with subquery within a subquery you can write like this it is possible one subquery within this it is possible one subquery within another subquery you can write down sir subquery for this see ward I'll show you now just Wait first let me finish this that's what I'm telling right now ward the same the situation will come here show second sorry employee details who is getting second max okay so then can we write like this select star from emp we write like this select star from emp where you get it down here now salary is equals to Okay, can we get this? This is giving Okay, can we get this? This is giving second maximum salary that query that I'm adding here that I'm adding. See there one subquery within another subquery an employee details who is getting an employee details who is getting second maximum salary 14,500. that subquery I'm writing one more subquery here. So how this will get executed? How this will get executed? Ma first How this will get executed? Ma first this will get executed. Okay. 16,500 will come. Then continuation to this will get 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 order. You have to go for that in a reverse order. You have to go for that here guys. Understood or not this quy understood or not? Ma guys please let me know. for more than maximum salary. No. Anything whatever you have to do that ex up to maximum salary only. The same thing we are doing here. Yes. Your calculation is correct. Whatever you are thinking that's correct here. 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 can that is possible. Okay. But we are not using any group by clause here. So using any group by clause here. So that's what 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 have to go for like this for this again you write one more where clause that's it clause that's it where salary again going for less than where salary again going for less than what my that max of salary from EMP what my that max of salary from EMP we are taking like this obviously you get third maximum salaries 11,000 is there 11,000 is there got it ward 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 max and min salaries. and min salaries. How we write a query for this? How write a query for this? I want an employee details who is in one query. I want maximum salary employee and minimum salary employees. Both two employees I want right now. Two employees we can write on over Two employees we can write on over there. Shall we go for like this? there. Shall we go for like this? Select star from. Can anyone try that? Select star from. Can anyone try that? Please try. I want an employee detail. So he's getting max and min I'm giving one clue also. You have to use in operator. Anyone? Ma, should I write? answer we get see current has given mark current has given answer that see there select start from emp where salary in just I'm taking like this here now you write write select okay one thing we do here okay from EMP. That is one query. Put a from EMP. That is one query. Put a comma. Then you go for that here. Select max of salary. Max of salary. Yeah. From EM. Okay. Then this is done. See that this one this is one method. So first word math two subqueries. So in one way class two subqueries has been written independent This will give you maximum minimum salary. This will give you maximum salary that works on in the inoperator. See there now you get those two employee See there now you get those two employee details. 16,500. The two employee details we got it there. it there. Guys, are you able to follow or not? Fine sir, why we are using brackets for getting values of quaries? brackets but it's not I think so optional guys here it's a name optional guys here it's a name no see actually here it won't allow you here's comma after completion of query semicolon will allow you so that is what see it is not allowing you to write a second one because of that if you keep that parenthesis okay now it works there got it navindra Navin Raj sorry yeah so this is the way the subqueries 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 all employees who are having who are who have who have above the max salary above the max salaries of what we can call retail department. H I want like this now. Retail department 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. the maximum salary from retail department and show all the employees who is getting above that maximum salary of retail department. 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 So select max of salary from EMP. See there now here where what from EMP. See there now here where what that is. So DBT is equal to what we can call retail. Shall we get like this? That will give you the see that 10,000 we got it that so then simply you go for that here select star from EM where okay salary select star from EM where okay salary in [clears throat] greater than this is above no I asked about that so that we go for here now about that so that we go for here now see there here there now which is from different departments 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 the group by clause we no need to add. Even if you add the group by clause that also fine. Sometimes group by clause also will come here. What is that? Uh see there this clause you can make it like this. So this one So this one usually we write like this. No group by what method is d I'm writing. So all department maximum salaries will come from that uh you get it only uh like what retail department group by uh maximum salary. maximum salary. So that you have to add it here. So this this is also will give you retail department maximum salary only. Yes. See there that is also fine. 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. See there. Now 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 there are different ways to write the queries that has been given. querying of this 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 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 how is everyone understood writing the how is everyone understood writing the subqueries guys? everyone understood. If anyone having any query, please post If anyone having any query, please post it here so that we'll discuss please guys. So I'm moving on to next topic, right? So this is about let me save this one like a subqueries 21st February like a subqueries 21st February 20 21st subqueries yes this is the one I'm just taking that yes this is the one I'm just taking that yes so going to another one yeah [snorts] so now the next point here now let's see That 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 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 go so for that I'll show you one thing that here 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 there number is there student name is there time 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 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 we have one that is three [clears throat] students are there here [clears throat] students are there here so now I'm taking that uh marks is one table you are having in this marks table. So what I'm taking you know table. So what I'm taking you know serial number exam subject one marks subject two marks total and here I'm giving role number something like u so what you say that here so quarterly is an exam okay some 56 78 exam we got some total here now let's go 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 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 student like this so what is that one here now let's say that whose marks it is suppose let us say that if I add 1,4 that if I add 1,4 it should not accept here because here one thing that guys here the marks what we are entering here the marks okay That marks are completely it depends on the the students who are who depends on the the students who are who are there in the by data table. 101 role number 10,002,003 is there now that we are taking this is okay. This is also okay. This is also okay because these role numbers are okay because these role numbers are there here but uh it is not acceptable. If I want the role number who are having role number 104 by data details can we get from this table can we get it from this table we don't have that we cannot get it that one so data 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 excel so that we are able to write but it should not happen on a data basis so that uh we have to make a relationship between uh these two relationship between uh these two columns. We have to make relationship between these two columns here that is okay. So whenever we make relationship between these two columns now here it is between these two columns now here it is a primary key here it is what primary a primary key here it is what primary key it is simple wayma that is it is a primary key and here it is a what in the sensema and here it is a what in the sensema here foreign key primary key and foreign key will go for So in foreign key column whatever the values you are entering this column will refer always the value existency in 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 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 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 you can so you can go with like country names and their 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 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 show you now there you delete this one here just see that so the relationships you can take in four ways what that is the relationship you can take it in now four ways that here first relationship is one to one is there now whatma that is one to one relationship will be there. So one to one in the sense there. So one entity here entity A is there then 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 toone relationship. What is that one to one to one relationship? Here see there now 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 student course. Can we make it like this? One student one course. one student one course one to one relationship can take that is here. So that is one we can take down there. So that is one we can take down there. So one more is there here that one to many 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 this is one just we can take down here take down here this is one entity having one entity having here a relation one entity having relation with the two other entities entities here two other entities. two other entities. So here you can go for this one. This is the one just we can take this is called here one to many called here one to many one to many that what is that here now one to many that what is that here now sales report just see there sales report consist what that is consist of customer and product details product details here product details here here this you can make sales 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 many to one also going for same thing that what mother is many to one also you can make that is that that is also one more point you that that is also one more point you will get there So there are three has been covered. Three has been covered here. That is 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 Many to many that we can take down that is. So this is one more just you can take down here. So how you can go for that is right now there so A and you make it see that and 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 getting is one thing just uh we are getting that. So A to C you can get A to D you 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 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 can anyone guess for that Yes. is one that see multiple employees are participating into multiple projects. Can we take like that projects are multiple employees also multiple? One employee, one project not one employee might be participating into 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, multiple products, products, okay, that you can take down here. down that right now. Yeah, this is the one now can 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 a primary key and foreign key concept. Let's go for working on that primary key foreign key concept by 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 we'll go for here now when it coming to this one guys see parent and child tables must having a common column that is the first rule yes in excel if you see that see so here role number column is there here also role number column is is there here also role number column is there that is one thing second point in parent table common column should have a primary key. So that is one thing. Suppose if you consider this is the primary parent 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. See here in primary key here you have a unique values but here you might have the duplicates. How it is in this table only just check that. So three students only just check that. So three students has been written quarterly exams suppose if half early exams has been written that okay again the role number will be repeated no role number will be repeated because of role number will be repeated because of that it's a half exams that it is what that it's a half exams that it is what here now half exams that we go for 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 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 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. Yeah let's see now that guys this is the example we'll make it that here now one point we'll make it like this. What is the first I'm taking parent table as a customer serial number unique that means customer serial number unique that means here it is a that okay leave it there customer name location account number primary key 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 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. is that? Yeah, here you are having that. So, make it comment that So, then here first I'm creating a parent table. So, that create a table So, that you write that is a serial number. It is into unique. Okay. Int unique. I'm taking that. Then C name I'm making that is where of some 20. Then location I'm making that. So where car of 10. So make it a default value. Okay. What that is hyd. So then I'm just taking whatma that is. So then I'm just taking whatma that is. So account number. What mother is account number worker of some 10. So primary key duplicates won't allow but a null value is possible. One null value is possible. Primary key in this sense no duplicates no nulls 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. unique in the sense now there unique in the sense duplicates won't be allowed but one null value can allow one null value can allow you see that one null value can allow you see that guys I'll show you here for example okay here only you just take that it is not a don't consider like a relationship here only this one I got here now unique constraint what the unique constraint okay here I don't know one of the student role number fine no worries I'm writing name and I'm giving that and again I'm going for that yeah that's what that is I'm explaining yeah that's what that is I'm explaining ninth just see there let me finish this ninth 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 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 this sense again unique constraint violated again unique constraint violated here so that is one point you have hard 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 is a primary key even that null value also will not be allowed completely unique and not null I hope you got it that understood yeah that makes the difference here now yeah now I am creating parent table here yes I created create a parent table. Now let's go for that child table that so here create a table transactions I'm giving trans only making some shorten giving 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 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 1 2 3 4 it will go that I'm giving Okay. Then T type you just write on T Okay. Then T type you just write on T type. T type is work of sum 10. Then T date transaction date. It's a date column. You just make that is here. Now column. You just make that is here. Now then amount what is the amount has been? So one second that here account number you write on it's a [clears throat] vare of 10. I am right I have written that then 10. I am right I have written that then here what is another one amount just to 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 write down yes yes foreign key of account number. foreign key of account number. Okay. References. 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. 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. So now two tables are in relationship that is [clears throat] 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. So that is one thing we are having then So that is one thing we are having then so describe trans is the table I just created right now there see here you can see that is so if it is 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 you know here what that is foreign key you can by seeing the structure you can say that from which column to which column you have a primary key and primary key and foreign key that is there guys. getting right now. Okay guys that is one thing. Now let's 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. Insertion of data has to be done first parent table then what? Child table. Okay. Add some rows into the parent table. Add some rows into parent table. guys that here insert into what that is customer so you insert into what that is customer so you just go for serial number C nameo then account number location is a default one so that I'm location is a default one so that I'm skipping that here values serial number one customer C name that is here something like I'm giving some name something like I'm giving some name ACC 00001 I'm writing that let's have three accounts. Yes. Now this three rows are inserted. See there this three rows are inserted. See there right now there row 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 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 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 then transaction date let's say that here now last year 2026 - 02 - 10th date let's go for that it is a 10,000 amount okay I got it like this so this row will be inserted now 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 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. 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 that error statement? See there here error code 1452 cannot add or update a child row a foreign key constraint fails foreign key what that account number references to the this is so you are trying to add account number four but account number four is not present on the buy data table so it is not accepted That is what my here we are avoiding inconsistency data. We are avoiding inconsistency data. For example, if I go for that 100, 109, it should not accept because 109 is not presented here. Okay. So this is the one just we have to Okay. So this is the one just we have to go. Now you go for this is here one. go. Now you go for this is here one. Okay. Let's see that is right now. There is accepted. See that is now you go for account number 10,2 account number 10,2 let's go for that citor and here now some 14th date okay the something yes accepted and again you just go for that what you say that is this is something like a debit debit what that is debit some,000 rupees just have like this some transaction ction some random transactions I'm adding that some 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 understood or not guys please let me know if the primary creation is understood between the tables What is your query here? Uh if I want to create Yeah. Now come down to so here account number is primary key. So it won't allow duplicates and nulls. And when it coming to here you see first what I did here I have given all the list of columns in transaction table. I have given all the list of columns in transaction table that finally what I started here now see and now to this column I'm wanted to give foreign key there something some relationship which refers to the customer table of account number column so what I have written foreign key of account number what is this account number this account number is the column name of this is this 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 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 any other queries here guys please let me know I hope that guys please let me know I hope that everyone is able to follow right right now in this relationships so two points has been done first of all so two points has been done first of all uh is only one multiple columns should not get the primary key remember that one unique you can give any to any number of columns. Primary key is only one constraint for that one to the multiple constraint for that one to the multiple columns we cannot give primary key. So this is the one we go for a deletion suppose right now for updation is okay leave it that. So for a deletion what we have to do it in the sense see that guys have to do it in the sense see that guys first we have to delete rows from child table then parent that means ma for example just come down that means ma for example just come down here guys try to follow so select star from customer is there okay I'm just going for this is yeah now okay I'm just going for this is yeah now I wanted to details I wanted to delete pawani details here pani details I wanted to delete let's see that whether the delete command works or not here delete from customer jimma here where account number customer jimma here where account number is equals to here acc00002 I am writing let us see that here now whether it deletes or Not let us see whether it is deletes or not here. Now see it is not deleting that row. 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 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 delete from customer where account number equal to 0 to see error code 14 number equal to 0 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. 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 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 there is a transaction one is there and transaction four is 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 first delete all the rows from the child table first you delete all rows from child table then go on to delete the parent table like that it should go now see there here now what I'm doing here the same delete command first you delete rows from the transactions okay trans you go 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. 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 transactions for account number three in transaction table? Is there any transactions in account number three in transaction table? Ma 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 you can delete that is by keeping of this one here account number three there like this you want to see that. Yes you can do that one. See sir how it is deleted without deleting 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 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 particular uh account details but it is a little bit cumbersome task or not guys tell me little bit cumbersome task or not it because here we have a two to three rows or 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 to the parent table then deleting searching the data deleting in the sense cumbersome task. So database will give us one option. What is that? In the sensema, if you apply a delete command sensema, if you apply a delete command on parent table, if you apply a delete command on parent table, that command of that delete command has to delete rows from the parent table as well as its from the parent table as well as its child table relevant data also. If it is child table relevant data also. If it is like that, it will be easy for us. Got it ma? that flexibility. If you have that flexibility, then that would be fine. Anyhow, you have decided one row from Anyhow, you have decided one row from the parent table. So there is no meaning of that by deleting a row from the parent table by having data into the child table. So delete row from the parent table and as well as delete that parent table and as well as delete that relevant rows into the child table also. Then how can we do that? In the sense of while creating parent sorry while creating child tables while applying foreign key there is a an option called there are two options is there on delete cascade or on update cascade. On delete cascade or on update cascade is there. is there. What are that one? See ondee cascade. Onde cascade. When we apply this option to a child table. 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 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 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 we split that m? tables that so just have some few columns less number of column that would be better. Now here I'm creating a table DPD department that is department number department that is department number into primary key it's a parent table into primary key it's a parent table department name okay where caro sum 20 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 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 a sales department so then here 20 so then here 20 here now education department here now education department so then one more here 30 so here like what do you say that is research department like the three departments I added now let's have that if yeah fine let's have that if yeah fine now I'm creating here create table emp info okay here now emp number it is into okay here now emp number it is into primary key then eame care of some 20 I'm not adding all other columns my leave it that Okay, department number column I'm taking as a 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 okay foreign key of what we write department number references what I'm taking that dept of department number after writing this here you write number after writing this here you write on on delete cascade space on update cascade you have to write like this then close it you have to Write like this here now. Now insert the employee data here. Insert employee data. See there. Insert Insert employee data. See there. Insert into EMP info empa into EMP info empa name or department number. name or department number. Okay. So then go for values 1001. So Ramana. Okay. Here now. So department number 20. Okay. Here now. So department number 20. Yes. You add some of the employees. Yes. You add some of the employees. You add five employees. That You add five employees. That like we have some employees here. this one here. EMP info. I'm just taking that. Yeah, got it. So now you apply a delete 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 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 applying delete command on dep 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 Okay. Now you check the data in both tables Yes. Department 20 is not there. Now you check Now you check data in EMP table employee info table. Okay. See there now department 20 employees are deleted automatically but MP info on delete cascade. 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 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 now. 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 there. Sorry not that. le data sets is there in this leap data set. So you have very lesson five 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 side guys and please confirm that you are having or not these three Excel sheets. 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 there you have got so lec this is the SQL data sets folder this is so this This is what here and go to within that three folders existed. One folder you go for le data sets from that one lesson five a retail m management data set is there. There you have got uh data set is there. There you have got uh yes there you have got these three excel files. If anyone is not getting I'll share it If anyone is not getting I'll share it on into the chat box if required. on into the chat box if required. 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 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 these are the three tables having primary key foreign key relationship data ids see there first of all you go to customers table Nice customer data set or customers table. If you observe this customer table, you see there how what are the columns you have? Customer ID, customer name, customer location and customer phone number is there. This is one parent tables. 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 or something is there. 26 products are there. Fine. Then a product name is there. Okay. A price is there. Then stock is there. Then a category is stock is there. Then a category is there. This is different categories of table. Now here we have one table called sales. We have one table called sales is there. In the sales table In the sales table guys here now see there now ordered date guys. You see there that this is the sales report. It is a child table of both customers and products. Ordered date ordered number. See their customer ID. This is the foreign key column. This is This is the foreign key column. This is the foreign key column that then one 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 required 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 uh importing I'll avoid it that then 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 if the column names are having same names names product code product name I think it is product code product name I think it is yeah if it is same no worries got it then product code is there it one of the foreign key from the product table product name this column also we don't require require so totally we have a 26 products so 11 customers are there I think 11 customers but here how many in a sales table how many sales we got it here that see there now here totally how many rows are there my here now okay here row got 11 rows are there 11 in the sense 10 products has been sold out of 26 10 products has been sold out of 26 products. Yeah, find that. Okay, now let's start importing of these things my here. So now go to your database, right click on the tables. Right click on the tables. Go to the table data import vizard. So go to the browse. Do it on your side. This is my side. Do it on your side. This is my side. Do it on your side. Okay. first you take the customers part customer data set you select that is you'll be getting like this go to the go to the next and here see in a table spot to the next and here see in a table spot you keep table name as a customers what that is customers you just keep it customers then go to the next then next And next and go to the finish part until you make it finish. you make it finish. Yes, one table is there here. Yes, customers is the table. We have a customer that's leave it method. 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 that this time products. Select the products table and next. And here that products data set you remove that products. Make it products and go to the sales. Make it products and go to the sales. Okay. Here that is. So then go for sales guys. Here sales data set is there in a sales data set. Make it sales that make it sales guys here guys a small modification do it here. Now in this situ situation okay in this situation uncheck this CNAME column P name column. Uncheck those two columns. Uncheck that C name those two columns. Uncheck that C name and T name column and go to the next and and T name column and go to the next and next and make it finish. Yeah guys, now my point is right now here let's see joins concept is joins. So now the joins concept completely we will work it on 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 majest see there the process of retrieving data from the process of retrieving data from multiple table when those tables are in when those tables tables are in relationship that is see we can retrieve data from multiple tables but uh not taking from random tables that take one student table one employee table and retrieve the data from those two tables. It's not possible like that. Okay. So when the table and tables are in relationship that means primary key foreign key from the tables you want some valid data you want to retrieve that in the sense then joins process can use it. As you know that when you are having tables in relationship when 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 parent and child table that okay that is one thing you have to remember. So whenever you observe here the tables which we have taken there. Okay. So here just see here customer ID is there in a customer's table and uh in products customer's table and uh in products table product ID product code is there product code is there. So then uh in a sales table in a sales table must see there customer ID it's a foreign key. Product code it's a foreign key. common column is there here those two are the parent tables. This is the child table you are having. Okay. So like that. So when the tables are under joining sorry when the tables are under relationship by using join process you can retrieve the data. You can retrieve the data here. Yeah. How we can go for retrieving data from multiple tables using joins. This is the syntax you have using joins. This is the syntax you have to follow. This is the syntax we have to to follow. This is the syntax we have to follow that. See select what that is. See since we are getting data from multiple tables since we are getting multiple tables since we are getting data from multiple tables here. So on column you want to retrieve that you need to go. So table one dot column 1 table 1 dot column 2 table 1 dot column like this again another table called like this again another table called table 2 column 1 table 2 column 2 like this you have to mention. Then afterwards from table one join type table two table one join type table two and uh on join condition this is the process you have to take join condition in the sense what he gave here what is a join condition here let's say join condition is a condition join condition is a condition has to be placed a 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 column of joining tables. What how the condition should go on table one do column equal to table two dot common column. example, customer dot customer id equal to sales dot customer id or else products.p product code equals to sales.p product code. It comes like this. While writing queries you will come to know. While writing queries you will come to know here. Then what is a join type? Here we have a join types are there. Here now what is this join type? In the sensema here there are a In the sensema here there are a different type of joints as follows. One is my here inner join is there. Second one outer joint that again classified into two types that left outer joint right outer joint. Then we have a cross join then we 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? Whenever you go for the inner join Whenever you go for the inner join mazee, see the process of the process of retrieving rows from joining tables for only matching values are rows exa exist in common column between parent and child tables that is joining tables. Joining tables that matching rows should go here. Matching rows. go here. Matching rows. So that's what we do here. Now let's see that how it comes right now there inner join how it comes there inner join how it comes one second guys so yeah 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 joint see here customers and products are parent tables Sales is a child table. You cannot write a join query between customer and products. That's not possible. Either you write customers table and sales join or else products and sales join. Or else by combining these three join you can write. First we write one join carry for two tables. Okay. So now so anyhow you are seeing here now see there h what is this customer id is there h what is this customer id is there now I'm writing a join query join see there see there between customers and sales. between customers and sales. Okay, this is so now first you go to Okay, this is so now first you go to write now select store from sorry 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, write on select customers dot star, products dot star sorry not products products dot star sorry not products very sorry guys sales dot star okay that very sorry guys sales dot star okay that is from see there here customers sales then uh on what that is customers do C then uh on what that is customers do C ID is equal to sales dot C id ID is equal to sales dot C id you should get like this here customer's table when you forget that select star from here year What is the star represents here? All columns. Now I'm bringing all columns from customers table. All columns from sales table. That is customers are there here now? 13 customers are there. We have how many customers are there. We have how many customers? 13 customers are there. Okay. Now this is what we are having. Yeah. Fine and you go for the sales table. You Fine and you go for the sales table. You go to whatma that is sales table. You go 10 rows are there. Then uh that means 10 products has been sold by uh here and 10 customers has been purchased. 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 retrieved okay we do one thing just see there here and copy the values of this column here and copy the values of this column yeah I'm copying this so how it works I'll tell you ma how it works I'll tell you yes I added 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 Now go to the sales how the inner join works that is I'm trying to explain you take this column okay and I'm copying that and here you just paste it that is yeah and this is for what here now and this is for what here now sales it is yeah now the comparison goes either from here to here or here to here it doesn't matter doesn't matter that how it works books there only matching 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 4 once is there yes we have here so that particular details will come then 121 you check 1 to 1 is there or then 121 you check 1 to 1 is there or not yes it is also there yes it will not yes it is also there yes it will come then 1216 is there any 1 2 1 6 I come then 1216 is there any 1 2 1 6 I think so. No. Then 1 2 4 6. Yes. 1 2 4 6 is there. Fine. Then 1 3 1 3 is there. Is there Fine. Then 1 3 1 3 is there. Is there any 1 13 1 3? No. 1 9 1 0 is there. Yes. 1 9 1 0 is there. Then 2 1 2 3 is there. 2 1 2 3. Yes, it is there. Here. Then 3 4 52 is there. Is there any Here. Then 3 4 52 is there. Is there any 3 4 52? No. Then 3 921 is there. Yeah. 3 3 4 52? No. Then 3 921 is there. Yeah. 3 921 it is there. Here see that. Here you have. Then 5334 is there. Then 5334 is there. 5334 is there? Yes, it is also there. Yes, that is. Then 9021. Is there any 9021? I think so. No. 9212. Yes, it is there. First one. So then now 9875 is there any? Yes, here we are having 9875 is there. missing right. Huh? Yeah. Yeah. That means so one product has been 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 so this this product this customer details won't come this is capable 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 okay this these are skipped remaining all because matching values are there that will come there okay that is the one thing just you have okay that is the one thing just you have to Remember let's see now here. So go to so this one here now executed this what execute this process. Yes. See only we have 10 rows. Yes. See only we have 10 rows. 10 rows we are having that is just wait customer ID, customer name, location, phone number. Then see there ordered date, order number, see there customer ID and whatever that is product code which has been purchased this address and this is getting that is but here it is not complete it's not complete informative just just randomly I took all columns that okay here but the question goes like this what is the question goes in the sense here the sense here show see there show all customers who show see there show all customers who are purchased What that is who are purchased products which products purchased we don't require here who are purchased the require here who are purchased the products 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 okay customers dot you just go for okay customers dot you just go for customer name that means C name customer name that means C name that is one thing that we are having that is one thing that we are having okay so customers dot then what that is 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 want in the sense here I want order date then sales dot order number then sales dot order number okay that is then sales dot quantity what method is quantity then sales dot amount so like this you have to go what are the products they purchased we don't require that don't require that any column is missing C name actually any column is missing C name actually it's not C C name it's C name. it's not C C name it's C name. See there guys here. See [clears throat] this is these customers has been done some purchasing. purchasing that is see the who is that here? Niha Kerala. Nha from K Nisha from Kerala has been purchased has been ordered some quantity and amount like this you are having this is what guys is it understood guys this is what guys is it understood guys is this query joint compares here please let me know guys I have been explained in Excel how the inner join works here by copy pasting of all these things there. one more what is that you know here 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 the dish? So then how can we go for the dish? So [snorts] for this here the products is there right now. Okay. So now what I'm taking that select products dop name. Okay. Then products dot price. dot price. Then products dot stock. Then products dot stock. Okay, that is then products dot uh that category we don't require. That's enough. Then go to the sales table. Sales table is what? You just take only these two. 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 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 then what method is on products dot pers code is equal to sales products dot pers code is equal to sales dot p code that we need to go this is one Again name is unknown what happened by Products is spelling wrong. Products are spelling wrong. Products are spelling wrong. No spelling wrong. Products. See there. See that here now we got 10 here that is See that here now we got 10 here that is so corn is the price is 50 20 stock is there 10 has been sold see 50 into 10 there 10 has been sold see 50 into 10 500 10 price is 10 rupees and it quantity two items has been sold 20 rupees total stock is like this see then rupees total stock is like this see then only what mother is kiwi has been given twice that mean two different customers has been uh taken this kiwi might be going for like that in a table but whatever it is there so this is the 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 for that but one more point I want to discuss here simple point in this one Here every time we used to write a table name customers dot customers dot products dots sales dot something like that it is somewhat cumbersome one when the table names are very lengthy so shorter names is okay so that these queries you can write on with a table aliases a table aliases for a table you can have alias name okay now for this one you can have table alias names here alias names here using the table alias table alias [clears throat] should go what is that table alias in the sense ma what is that table alias in the sense ma 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 take one or two alphabets as an alias name so for products I'm taking P is an name so for products I'm taking P is an alias name for products okay P dot P 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 go for S dot quantity then S dot amount like this then what is P what is S we need to tell we need to tell that so how need to tell we need to tell that so how in the sense my here products as P in the sense my here products as P inner join sales as yes so it is original table name and it is alias name and it is original table name sales it is an alias name here also you can keep like that yes okay then p something like uh 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 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 so your query becomes shortened that's it okay so I hope this is understood this alias names all queries you can add with alias names all queries you can add with alias names the queries whatever we have chosen all these queries you can add with aliyas names only with aliyas names only This is make it C dot star make it S dot star. So customers as C inner join sales as yes then here S. C id C do C ID then here S. C id C do C ID see that with the alias names it is 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 alias asis right now been taken column alias asis 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. doing guys. Is it clear with aliyah's names? Yeah. Now so here join with three tables three or more than three tables but of course we have a three tables is but of course we have a three tables is there I'm writing three tables part I'm writing three tables part guys listen here three tables so first of all you have to make it observe that from the three 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 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 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. 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. 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 what that is yeah order number order 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 define it whatever the columns you want that is Okay. All the columns from the three tables that we took here. Three tables we took there. Okay. Let me do it tables we took there. Okay. Let me do it one thing here. Yes. Like this. Okay. So now see there guys from customers as C inner join. guys from customers as C inner join. Okay. Sales as yes. So you cannot make I already told you you cannot make join between customers Okay that one point you have to remember. Okay this is one thing just we go from customer as C inner joint sales as yes customer as C inner joint 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 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. One more join has been done with the sales and products. How it should done sales and products. How it should done here? Yes. Here again you have to write here? Yes. Here again you have to write uh inner join products. uh inner join products. Inner join products as P on see there P dot P_coder P_coder is equal to S dotp code you have to 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 for products and sales then sales and customers anyh how you write it doesn't customers anyh how you write it doesn't matter complete data here three tables join it is see there the customer stiffen is the customer has been purchased corn to it's a 50 rupees okay like 10 products he has been purchased The amount is 500. He has been ordered on this date to this is the order number. So complete details you order number. So complete details you have so products you just go for a kiwi kiwi twice as came because one one order from twice as came because one one order from the moan one order from Oliver that is this is order number five this is order this is order number five this is order number three you have like this Guys, is it clear guys? Writing the query join query for three tables. Guys, please let me know. I hope that everyone able to follow 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 starting. Yes, exactly that's it only attaching that is yes that's what only here you'll get the clarity on this kumar sorry current uh next few minutes just sorry current 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 come to know but your understanding is fine that correct okay that's what I'm telling you first of all while writing join query First of all, you have to make sure that which is parent table, which is child 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, first what? this sequence then afterwards this sequence sales to then afterwards this sequence sales to products sequence so please let me know anybody having any queries on behalf of thisma here remember that if you're writing a join queries So on this join queries what about other clauses we discussed earlier in select clauses we discussed earlier in select statement where clause order by clause group by clause having clause these all can we implement yes of course we can the requirement should go for here now here we are just checking on only sorry join checking on only join for example let's see that is I do one thing go to this product sir go to this product sir Okay. report. Suppose if anyone ask that. So, that is here. Now, show all the customer and products all the customer and products products of perfume category. Let's see products of perfume category. Let's see that is perfumes category. If we write like this then how can you go? So first add where clause where it's a perfume add where clause where it's a perfume from the products one P dot category from the products one P dot category P dot category is equals to what should P dot category is equals to what should we write me that perfume where clause yes Hey then spelling mistake is there then spelling mistake is there no correctly. No perfumes has been sold here. See there? No perfumes is sold here. See there? No perfumes is sold here now. Yes. Now you write on there. That's correct. Ma'am, no perfumes has been sold. sold. Snacks. Now you'll get it. See that snacks has been sold. This 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 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 retrieving from joining tables per matching and non-matching. What that is matching and non-matching joining tables that goes here that is but again it has been divided into two types left outer join and right outer join is there left outer join and right outer join. What is this left outer join in the sense comparison from left side table or left all values of left side table of left side table to right side table right side table in join statement in join statement here in join statement here if value present uh in left side table. Left side table but uh not present in right side table. Excuse me. That row also retrieved. That row also retrieved. That row also retrieved. That is what we are have. This is so what that is left side table that so let me write one example for this. So now here guys let's see that is a left order join take the previous examples only. So leave about three tables first go for with the two tables. Uh first one what take decis the take down to the products left order join what is the question here now show here now show sold and unsold products info suppose sold and unsold products info uh both are Both are same. 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 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 left outer join 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 [clears throat] in here. This quiry you'll get it here in here. This quiry you'll get it here that 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. Get all product codes. 26 products codes is there. No, you bring it this one here. Yeah, here. Yeah, this is the one. This is what here. Now 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 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 one guys you see this line guys you see this line go for this line here uh for the word left outer join what is the left side table what is the right side table what is the left side table what is the right sided table for the left outer right sided table for the left outer join word on that statement yeah what mother is products is the left side table sales is the right side table is it correct 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 here so this is what don't confuse guys in this China. 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? What is the left side table and right side table? it or not? Guys, everyone productive, right side table is what? That is sales. That is sales. So, now one second guys. So what I explained here left out order join means comparison from see comparison from all values of leftsided table to right sided table in join statement here if values are present in see if values are present in leftsided table but not present in the right side table that row also retrieved which means now you go to excel product number one. The comparison should go here. The comparisons should go here. That is is the product number one is there in a is the product number one is there in a sales part. Guys, tell me if the product number one is there in a sales part? No. Though it is not having the row will 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 number four is there. No, 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. 8. No. 9. No. 10. No. 11 is seven. No. 8. No. 9. No. 10. No. 11 is there. It will come. 17 is there. Okay. there. It will come. 17 is there. Okay. This is then 16 also there. This is then 16 also there. Okay then 20 what mother is 20 also will be having that here so 20 20 twice is there now then like this you are having matching non-matching all non-matching rows will come with the null values so that is that means so here all values so that is that means so here all values just see Here 1 to 26 all values are 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 one thing just you have to go see that output mark left have to go see that output mark left outer join no yes see there we have a 27 rows see product number one is not there so so this is the sales Sales part no quantity no amount cannot find only product table we will find the details so that is coming there see totally wherever it is not having values in that will come into the nulls that will come into the null here matching non-matching both the values matching non-matching both the values are coming here guys getting not my Okay guys, is this query is understood or not? Guys, please let me know matching non-matching. here? If it is understood then the conditions has to keep it that but just when I'm writing only core line just when I'm writing only core line only no okay I'm writing only core uh case statement is there we will do it that is finish the topic then we write some queries. So same thing that here suppose 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. left outer join vice versa to left outer join which vice versa to left outer join which means what here now the comparison goes yeah all values of right side comparison goes from right sided table to left sided table so here here I'm writing right what I'm writing my right outer join I'm writing here. So here how the comparison goes in the sense if you go for that is the comparison from sales to products it the comparison from sales to products it goes because right side table is sales left side table is products. So that comparison goes from sales to product. comparison goes from sales to product. Sales to product in the sense 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 is there 17 17 some 17 will go yeah 11 is there 17 17 some 17 will go two will go then 20 20 also will go for 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 for here then 25 last but one will go three will go. So all values are completed from right side table to left side table. See all values comparison of right side has been completed. There is no values to compare from right side table to left side table. So even though left side table having excess of values it won't be compared because comparison is going from sales to product. So all values are completed 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 so that sold sales report only you get that that see you got only 10 rows see you got only 10 rows you got only 10 rows here that is you got only 10 rows here that is this is here now right outer join. side table not present on the left side table it will bring if it is right outer table it will bring if it is 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 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 side table will be compared to the right side table if it is right outer right side table if it is right outer join And you change the positions of tables again the output will come reverse. there. So here now here you go for this and here what I'm taking you know and here what I'm taking you know this is I'm taking here this is I'm taking here and here I'm just going for that uh here and here I'm just going for that uh here now tell me the result now if I write like this what is the result Yes. This query result is as right out join one 10 rows. Yes, exactly. This you make it left outer join. Sorry. Position change my here. 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 depending on position of table that in a join statement. right outer joints right clear or not guys. Yeah, let's go for another uh join. Cross join is there. 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 Here let's write on a query without join condition mark what will happen if you write that so take this is only take this process only here remove this take this process only here remove this join condition and here you write on cross join China. Yes. Like this here guys listen that uh what do you say that is um yeah how many rows are there in a sales table or products table guys? 26 rows we have right? How many rows are there in a products table? 26 rows. How many how many rows 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 this one here what happened here? What happened here? Ma you guess that what happened? What is the result of this one? What happened? 26 rows into 10 rows multiplied both 26 rows into 10 rows multiplied both tables 10 26 into 10 260 rows. So see there one product has been mapped with all 10 rows of sales table. See there now here see the result. Yes these are the 10 is there. Okay that is one thing then one more product. Then one more like this we are product. Then one more like this we are have actually this is called here have actually this is called here curtsign product. curtsign product. Curtsine product we can call that. That means 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. table like this. Okay. Buth that is inconsistent data. Okay. Buth that is inconsistent data. From that one to retrieve actual rows we From that one to retrieve actual rows we are we have to keep a join condition. 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 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 even for the cross join even for the cross join so cross joiner is not useful for production it is not useful for production that it is the join has been given to does just to make you 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 one's output will be generated that's called output 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 join condition has to be retrieve that join condition has to be kept condition there is no anything that except cross join. But cross join is not useful for production. Just for our understanding perspection they have understanding perspection they have given that I'm not getting any responses from others. that's okay. Ma, then we go for right now. Self join. now. Self join. We go for what here now? Self join here. So what is a self join here? What is a self join my her? uh implemented on single table on single table when column to column relationship uh column to column relationship exist. column to column relationship exist. Self 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 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 what that is column to column relationship sometimes you will 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 old table. So we are working on emp table. No have you observed that 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 have a employee ID column is there and have a employee ID column is there and you see the last column manager id manager ID is there these two columns are having under relationship here it is a primary key and here it is a foreign key won't mention here we won't mention here that but uh the thing Please write now 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 these two columns are clear with you that so now we go for self join on this one what is the what is the occurrence will come here in the cells now see I want to know that who is working under whom I want employee name or their manager name I want like that what I want in the sense what I want in the sense Manager name and employee name I want. Yeah. Select start from employee is that is what see the relation here 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 the E583 employee name? You just bring that one here. E583. Yeah. So this is Roy manager is Jonet Hale. I want like that. Okay. I want like this. Janet H manager is okay. Like E002. Who is 002 here is okay. Like E002. Who is 002 here that? Okay. Like Cynthia Brooks. I want like that. So how you show that is yes we have to 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 calling self join in the sense because you are writing join on single table. Again here you have to write inner join and outer join. Here we have to use again what that is inner join or outer join that is one thing. Sir how you take one table as a two tables in the sense because we have a table alias is there no table aliasis is there that based on the table aliasis you can do that what query here query here so select see there e dot first name you do one thing make it concatenate concat of this is the function both columns I'm concatenating Okay. That is one thing by putting comma then concat 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 EM emper join okay emp as a m okay emp as a m on e do empore on e do empore id is equal to m dot manager id. You have to write query. This is the self join query. This is what here now self joined query it is it is see there now manager name this is okay you do one thing manager as manager. Now you took this is see that Ry Collins is an employee here. Now he not given that queria. is Janet Holl. Even if you want you can check that I I Even if you want you can check that I I removed See Ry Colon is an employee. 583 is what that is here 583. that is here 583. Jet Holl is a manager. So Janet Holly employee manager is what? Cynthia Brooks. Okay. Cynthia Brook manager is what? Arthur Block. Arthur block themselves is Arthur Block. Arthur block themselves is a that is the same thing you can check a that is the same thing you can check here. this is so John at holiday see Cynthia brokes. so John at holiday see Cynthia brokes. So last we are having this 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? and others also ma'am actually here I haven't get any messages or any responses from Sri Di Svi are you there in the meeting today are you practicing or not everyone ma But actually you have responded last 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. So this is about completely joints chapter the core concept of joints. But overall if you observe that cross join we won't use remember that is one thing we didn't required self join goes for rare cases. So very rare cases that when we are having column to column relationship between tables so that we have lef over between tables so that we have lef over two inner join and outer join that outer join also left outer join and right outer join that's it. So that is the one just we are having. I hope that overall concept is understood hope that overall concept is understood right joins concept. So here primary key foreign key depends on that joins right is it correct? Primary key foreign key depends on that joins is there. I hope it's clear with everyone. Okay. So on this topic uh we'll have some queries tomorrow first uh first half an hour then we go for new concepts tomorrow. Okay. If you have any queries please let me know. Tomorrow's topic I'm writing my here. Tomorrow's topics are here. Uh uh what Tomorrow's topics are here. Uh uh what do you say that uh view is one concept is there. Views is one concept. Views is one concept. Indexes is another concept. Then stored procedures. Stored procedures is another concept. We are having like this. No, no, not this one. Not stored procedures that is also will be there. Before that uh user creation creation user creation uh when go to the user user creation uh when go to the user creation grant comma revoke creation grant comma revoke revoke this is one we are having then roll back and commit to okay this is there here now so these things first we'll keep it one thing this all we keep with top side and that this we'll go for top side then this is the order we will make it this is the order we go for that yes is my very first one like we are having here so in our command so that means before going to the programming in our command so like we have create we have worked on create alter Enter, 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 revoke that grant and revoke comes under the user creation. the user creation. So that is so after that we go with views and all that. Let's see that how we can go for it. we can go for it. Okay. So that is the very first thing here like go what is a commit and roll back? What is a commit and roll back mat? So whenever we go for commit and roll back the commit and roll back are essential transaction language commands. Just see my used to manage data integrity by uh either permanently saving changes made uh made during the transaction or 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 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. This command restores the database to its state before the transaction started or before the last commit. Guys here actually yeah I think this is what we are having the key differences okay we will discuss that while working okay we will discuss that while working on it just come down to here okay uh this is okay find that uh so we are having so much of tables are there here okay this is one thing uh let's see that anyone table like EMP info table we'll see that so select star from EM info this is the table just we are uh yes like we have some three this is n of the like we have some three this is n of the table and n of that okay so usually guys listen carefully here usually on this table that means you 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 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 of for Okay, control in the sense of for example you added some rows example you added some rows but within that rows some of the rows or all the rows so made have gone some mistakes 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 we wanted to go for that removing off all rows here that so then uh usually what we do in the sense no we go for a delete sense no we go for a delete we go for delete that that is one thing we go for delete but uh without doing that suppose you added some rows that 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. 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 reason my point that that 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 modifications I don't want that. I wanted to the previous state then I 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? 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 have done it so that I wanted to made that transactions permanently in a database permanently in a database so that permanently in a database transactions in the sense either insert or update or delete you remove some rows I don't want to get 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 added some rows. I added some rows to the that I wanted to make permanently in the database. Then how? So here make permanent changes on the database or make undoing the transaction just 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, update command, delete command we will make some actions. So make permanent changes on that or else make undoing of that actions. So how to do that is okay for that one we have a two commands are there commit and roll back. 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 very important once you made commit then after that if possible. Okay. Then again you have to go for other commands like delete or something that this is what but for this so if you want to use these command that means make permanent changes or make undoing that the changes or make undoing that the databases part basically see till now have done yesterday also we have done a session we created lot of different tables that but uh anyway we haven't used that commit or roll back commands why because in the sense now Here basically SQL will go for auto commit. That means once you add a new row permanently added to the table. Once you delete the row from table permanently deletes once you go for update permanently updates that it goes this way. So but if you wanted to use these commit and roll back based transactions. So first of all that auto commit you have to be make a switch off that auto commit option has to be switch off that. Okay, for that one there is some set command is there. That set command we have to be use. So before starting your transaction s so here we have to go for 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 here we will make it that set auto commit. see there now here this is set auto 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 on set auto this is or set auto commit off this has to be take either off or off this has to be take either off or 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 zero it this is here now or else you just go for better zero okay or else you just go for better zero okay that is one thing transactions Now we can do okay now we can do transactions that so that is so now see there now you go to this table guys now you go to this table now I'm trying to insert some rows guys you see there to insert some rows guys you see there that insert into emp info so that values 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. Okay. So 20 is not there. So that make it 30. Make it warmer. That is 30 here. Now let's see that is uh see there now so now the rows are added see there here rows are added here and see there here rows are added here and if you go on that one let's see that if you go on that one let's see that select star from emp info in the sense here you will get it that Right? See that it is showing old data and the new data both are showing here. Okay. That means it has been saved in the database table but not permanently. It maintains. So what we can call memory in the caching from the cachy memory in the caching from the cachy memory these rows are there. So now here we 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 simply what you need to do it in the sense roll back use the roll back. There is no other syntax simple that is and make it this is see roll back executed. Now you check whether those two rows are there or not in the database. See there those two rows are not there. So from the caching memory that rows has been removed. Just know what the operations you performed. Okay that is so once again you insert it. Once again you inserted that. Yes. After this you must to do here now if you want to make permanent one in the sense mother error commit the commit has to go for it now commit will made that permanent changes commit will change that permanent changes on the database. Yes we have changes on the database. Yes we have that. So now see after commit after that. So now see after commit after commit you do roll back you do roll back here just go to this one and make roll back. Let's see that whether the rows 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 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 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 see I did like this here now so rama rama is there no ramani Ramanica. Okay. There something like 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 see five rows are updated. So now whenever we go for this is see So now whenever we go for this is see there now here. So, EMP info whenever you go for this part here now see that all has game raandic but we have stopped the permanent transaction so that right now here now we have an opportunity what is that made roll back roll back okay so now you see there the old data will come that see there that is old data has update if you go for commit and then go for roll back it won't come the same 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 [clears throat] 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 that rows. So now you made roll back all the deleted rows will be back into the table. See there now will be back into the table like this. So you will be having that is Following or not guys please let me know. 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 commit and roll back. Okay. So that is what so when we added a new row it will be added permanently to the table. When we modify permanently modifies when we delete permanently deletes that is it goes in this way. So back commit commit and roll back first of all you have to switch off that auto commit. So that to set auto commit equal to zero you have auto made it then only to zero you have auto made it then only you can get set 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 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 where emp is equal to 1. You go for that sig is equal to 1. You go for that sig details. I'm deleting right now. Yes I deleted. 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 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. Yes. Once again you delete. Yes, I deleted. Okay. See in the database table not there. Now made commit. Now made what? That is commit. You made it. Now the row has been deleted 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 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. 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 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 okay lot of things in the sense this commit and roll back just listen this commit and roll back just listen this commit and roll back uh I have been used till now on only one statement. Ive used 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. used delete and commit and roll back. Okay. Now see there here. Okay. Now see there here. Yeah. Just come down to here. any table. Okay, take that EMP table that. So on this EMP table first what I did you know that here insert then uh I did now [clears throat] here uh some modifications. uh some modifications. Okay. Some modify Okay. Some modify then after that uh I did some delete or I did this insert and modify for example that means couple of rows has been inserted and I have seen some data and on the data you modified it some of the rows where it has gone some mistakes but you haven't done anywhere the commit okay so if I go for roll back what will happen if I go for roll back what will happen in the sense because of you haven't done 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 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 work it won't work that is so remember that one point that one point so before commit whether you do row uh new row adding modifying existing rows deleting some rows or something you did it any transactions okay after that you made commit everything will make permanent okay now that is one thing or multiple transactions what you do that is one thing but if you get back uh suppose if you go for roll back before commit whatever you did all the transactions will be unders that's it that is going to be undoing that is going to be undoing here that is okay it's not a part of one command or one thing that is here now I shown you one that you add multiple rows and you do you did some modification ations and make it roll back everything will go off. Everything will go off that. So but if you make commit everything will permanently stays in the table. After you have to go for a delete under commands that is we have to go for in that way. So that you should be cautious that while working on these two commands when should when it should go commit when should when it should go roll back that it should be like what you say that is cautious [snorts] 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 over here we have to make this one we have to switch off auto commit equal to zero then when you switch off set auto commit equal to one then you have to go for that permanently doing that is but these things permanently in work menu you can do some permanent settings on this even that update commands and all that is so where in the sense we just wait that here we have some settings is wait that here we have some settings is there in the settings part we can do yes navindras it's a temporary one only it's a temporary one only that is that it won't be stay even suppose like see you are working on that you added You added some rows that suddenly your system has restarted all rows will go off. All rows will go off that that means all the transactions what you did before commit to 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 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.