---
title: 'SQL Complete Course in X Hours 2027 | SQL Course for Beginners | SQL Full Course | Simplilearn'
source: 'https://youtube.com/watch?v=qZoUkkdqYB0'
video_id: 'qZoUkkdqYB0'
date: 2026-10-27
duration_sec: 27611
channel: 'Simplilearn'
---

# SQL Complete Course in X Hours 2027 | SQL Course for Beginners | SQL Full Course | Simplilearn

> Source: [SQL Complete Course in X Hours 2027 | SQL Course for Beginners | SQL Full Course | Simplilearn](https://youtube.com/watch?v=qZoUkkdqYB0)

## Summary

This video is a comprehensive SQL course for beginners, covering database fundamentals, SQL commands, and practical data management using MySQL. It aims to equip learners with the skills to write queries, manage data, and understand database relationships, from basic to advanced topics.

### Key Points

- **SQL Job Market Demand** [00:22] — Thousands of job openings for database administrators and architects are expected annually, and SQL remains a valuable skill across various roles.
- **Course Outline** [01:16] — The course will cover database fundamentals, SQL commands, keys, constraints, joins, advanced concepts, and MySQL workbench.
- **Data, Process, and Information** [04:40] — Data is raw facts, process is the work done on it, and information is the meaningful result. Example: constructing a house from raw material.
- **Why Use Databases?** [21:00] — File processing systems like Excel have limitations: limited storage, poor security, and difficult data access. Databases overcome these.
- **Database Structure** [31:27] — A database is a collection of interrelated tables storing data about an entity, such as a school or bank. Tables contain rows (records) and columns (attributes).
- **DBMS and RDBMS** [42:11] — DBMS is software to manage databases, with key operations: Create, Read, Update, Delete (CRUD). RDBMS adds relationships between tables.
- **SQL Commands Overview** [01:07:29] — SQL commands are categorized into DDL (CREATE, ALTER, DROP), DML (INSERT, UPDATE, DELETE), DRL (SELECT), TCL (COMMIT, ROLLBACK), and DCL (GRANT, REVOKE).
- **Creating Tables and Data Types** [01:21:29] — Use CREATE TABLE with column names and data types. Data types include INT, VARCHAR (variable length), CHAR (fixed length), DATE, and TIME.
- **Constraints in SQL** [01:53:24] — Constraints ensure data integrity: NOT NULL (no empty), UNIQUE (no duplicates), PRIMARY KEY (unique and not null), CHECK (value range), and FOREIGN KEY (relationship).
- **Data Manipulation Commands** [01:37:08] — Use INSERT to add rows, UPDATE to modify values, and DELETE to remove rows. WHERE clause is essential for conditional operations.

## Transcript

place an order, log into an application, search for a product, or open a business report, data is being stored, retrieved, and updated behind the scenes. But have you ever wondered how companies manage millions of these records without losing
e-commerce platform find your exact order? How does a bank retrieve your transaction history? And how does a data analyst extract only the information needed from a database containing millions of rows? The answer often
starts with SQL. According to the US Bureau of Labor Statistics, thousands of openings for database administrators and architects are expected every year over the coming decade. And as businesses continue building applications,
analytics platforms, cloud systems, and AI solutions, managing reliable and wellstructured data remains extremely important. That is why SQL continues to be a valuable skill across roles such as data analyst, business analyst, software
developer, data engineer, database administrator, and back-end developer. Hey everyone, welcome to this SQL full course, mastering databases, queries, and real world data management by simply learn. In this course, we will start
with the fundamentals and gradually move toward more advanced SQL concepts so that you understand not only how to write queries but also how databases actually work. Here is what we will cover in this full course. Database and
DBMS fundamentals. Getting started with SQL. Creating and managing data. Retrieving data with select. Filtering and sorting data. Aggregate functions and grouping. keys, constraints and relationships, SQL joins, advanced SQL
relationships, SQL joins, advanced SQL concepts, MySQL and MySQL workbench. And throughout the course, we will use practical examples so that you can actually used in real world applications. By learning these
cleaner queries, organize data effectively, extract meaningful information, and work with database systems more confidently. Also, if you are interested in building a career in data analytics, do not forget to check
out our data analyst masters program by SimplyLearn. This program helps you build essential data analytics skills through hands-on learning and real world projects. You will learn important tools and technologies like Excel, SQL,
PowerBI, Tableau, Python, and the data visualization techniques through practical projects, case studies, and expertled sessions. You will learn how to collect, clean, analyze, and visualize data to generate meaningful
business insights. You will also gain experience working on industry relevant projects that can help strengthen your portfolio and prepare you for data analyst roles. So, if you are looking to start your journey in data analytics or
upgrade your existing skills, this program can help you gain the knowledge and practical experience required to succeed in this field. Now, let's get succeed in this field. Now, let's get started with our SQL full course. By the
end of this course, you will not just know SQL commands. You will understand how databases are structured, how information is stored and retrieved, how tables communicate with one another, and how SQL is used to solve real business
and application problems. Question one, what does SQL stand for? A, simple query logic. B, structured query language. C, system quality language. D, software system quality language. D, software query list.
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 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
things. Then afterwards we'll have some concepts of MySQL. What is MySQL? The 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 an information? What is data? What is process and what is information? First of all we have to know about these three points here. What is data? So what
is process and what is an information that see why? Because the guys that here if you are going to start any task if you are going to start any task or something that so these three steps are very
important and it is mandatory by knowing or unknowing. It doesn't matter that whether you know or whether or 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.
It's a raw facts and figures. Raw material is not having any meaning for that. With the help of that raw facts, with the help of that raw facts or raw material, we have to go for some work on it. We have to go for some work on it.
Here now that work we call it as a process. The work we call it as a process that after completion of process after completion of process again
whatever the data we get again whatever the data we get uh 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 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 we gather for that? First of all, there should be a place then now sand, cement,
bricks and all whatever the material we gather to construct walls that [snorts] everything we can call it as a okay like what you can say raw material or also even a gopher iron steel also we gather that is by mixing of these all things we
construct walls. The constructing walls is not going to be taken randomly that means there is a plan to construct a house. According to that plan the walls will be constructed or else also the pillars will be uh like constructed and
process. process. How we are doing that processing? By using raw material 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 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 Telu or Hindi I'm a Telugu person actually okay I'm a Hyderabadi Telugu person so that I'm a Hyderabadi Telugu person so that uh Hindi Telu English any like Canada
Tamil like whatever the speaking languages are there first what you have 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 vocabulary we learn some words. So after learning of words now we have some set of words is there. Now we have
to construct a sentences. So if you want to construct a sentences using that words. So definitely you must aware of grammar. without grammar you can't construct a sentence in any speaking language
whether it might be English or Hindi or Telugu or Tamil or something blah blah once the constructor you are able to uh you are you constructed a sentence that's called as a a meaningful information is there within that the
processor data same thing whenever you come to the IT 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
without telling [clears throat] 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 what you are expecting from the what you are expecting from the computer? No, it's not possible. 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's
writing something on screen you want to say anything
That guys until unless if you have a query please send the query in a chat query please send the query in a chat box please guys. Yeah 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 the computer will be processed according
to the instruction what we have given and based on that output will be 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 one is learning the programming 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 so many programming languages are there in IT
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 instructions what is the output you with 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 but the thing you see uh you think in a high level way both of you I'm not wrong it's correct answer perfectly correct that but in a high level way once you
learn a programming language once you learn programming language then what do you do in the sense now we have to develop software We have to develop the software with the help of programming languages. We give
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 softares. Making softares that is sur also saying that one like communicating
with the computer it helps us to develop the software and solve the problems. 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 softwares will come to their 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 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 whatma that is softwares that that is one thing but one thing guys so while working with these softwares or applications suppose a banking people
are working with banking software but this banking software is deals with the 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 is 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 there guys please post it on chat box okay queries it on chat box okay queries yes fine here so now what is the point we concluded here so there are uh like a
two ways know dealing with IT sector one is about the programming languages for developing the application. Second one is database systems where we store the data where we store the data. Here let's see that I have given a simple 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 but we can call it as a program that here. Okay, that's about irrelevant as
concerned about the programming languages. So then one more question languages. So then one more question guys here. So why should we store some 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 timet in the form of sentence suppose in a schooling uh in a school or college or in education systems that uh so first hour some language so second Monday
first hour language second hour English third hour mathematics or something that so why should we write in a form of time table 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, Karen. 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. So obviously what happen you know until and what is the information is there that we can't understand. But uh when it coming
to the table format by seeing its rows and columns by seeing what it's rows and columns you can easily understand what is the task has to be done or what is 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 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 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 as MySQL or something not that I'm not expecting that one here
not that I'm not expecting that one here except databases. Yes. Naven yeses that except databases. 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
only especially these 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
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
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 uh you think in a big manner. Think in a big manner in the sense of 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
in the sense? No, it is not possible. 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 password in the sense but of course you can protect the data with the passwords. Okay, that is one more. But
the even the password in the sense you cannot open the file but I can delete 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 data. For example, let's see that I told you like I say a bank one lakh from that one lakh customer's information one particular customer
information I wanted to access based on account number or name or blah blah that is is it possible to get that using excel sheet 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 all that is very difficult it becomes 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 carefully that organizational data. carefully that organizational data. Okay. So that these all will overcome the database systems. These all overcomes what mother is database
overcomes what mother is database systems that here now. Okay. You see so these are the limitations here. Now from that limitations the database systems will overcome that C. So what does that say? A database is a better for managing
large data sets, complex data relationships or when multiple users need to access data, access and update data simultaneous. 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 Excel is not useful. Of course Excel is not useful for the organizational data. Excel is useful for personal data management. For me for example myself is one person. I wanted to go for my all daily activities. So
and I wanted to manage in in Excel sheet. It is perfectly suitable for me that only one for me or my family like for you for your family or else a small scale organizations
any small scale organizations is the Excel and MS access is useful perfectly useful but large scale data storage for organizations when that is I have given you only one lakh customer for IC bank but really is it is the bank having one
lakh customers no some cr of customers is there or else you take another industry like take a social media accounts Instagram Facebook is that Instagram details Facebook account holders having the data in Excel
way it's not at all possible that like education system universities school is education system universities school is limited but board state board central board universities so how much of data see this is completely about data guys
we are working on data completely. We are dealing with data that large amount 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 uh we can say that is so the entity might be any school or any organization any form that a banking
system school college or any insurance department a medical department medical department hospitals or else any like 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 See that guys for that I have taken here now databases student sorry databases school database I took here. Now I simply can and simply and roughly I just
categorized like this basically school having students employees and transportation. kind of transportation, library, these all different categories of school will be there. So when it come to the
students what kind of information we store in the form of tables or something 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
there 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 number has been allocated for that 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 section he has been joined, attendance
info, role number, student name, section, days working, days present and in the month of that month and year. What month that is, month and year you can say that is like you can have examination info, marks info, fees info,
examination 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, biodata info of the employees, job info of the
employees, salary info of the employees, salary info in the sense. So if you observe that as you know that your pay slips there is lot of information present on the pay slip basic pay net pay allowances deductions
okay like loans whatever it is there that is called salary info paylip part then when it come to the library that books info is there members info 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 malis 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
you were asking about what is a data set right si 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 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.
can 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. It can 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
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 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 tell me that what are the attributes yeah to identify the name to identify the person name is
there one gender like au 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 right? everything okay so that is that person is uniquely identifying in India with
the help of other card. Every Indian citizen is identifying with another card uniquely. Okay. So whatever the 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 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 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 so that is what I told you here a table is a collection of rows and columns okay
so here rows can be Call it as a here record also what that is record columns is often call 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 properties will become a collection of properties will become a row or a record a row or record we can
say that is for example let's say that here guys. So now let's see.
Yes. Suppose I'm taking now role number is Suppose I'm taking now role number is one column. Then student name is another column. Gender is another column. A is another column. Location is another
column. Like this we have. So now here uh so name of table you can 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 age is some 23 location something like
these all call it as what method what 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
can also going for this is a row you can call that one what is row also you can call it that is like you have so much of student details we entered here so much of student details Sir we enter like this there.
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 now please
an more answers from uh here now please give your responses guys
But we 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 so this kind of data table and all that is but 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 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 there should be some software tools are there okay within that software tools the very first one and very beginning level is right now there what you see in the sense so DBMS is there guys listen there
this is what just we are having DBMS. So what is a DBMS here? A DBMS a database management system we can call that database management system is a software
database management system is a software that manages databases 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 DBMS is. There simply one thing here now that the technical definition has been given there but in a simple words we can say that DBMS is the software tool for
working with the databases. DBMS is the software tool for working with the databases. Within that working everything will come. What is that? So
everything will come. What is that? So storing data, modifying data, removing data, retrieving data, lot of activities we do, lot of activities we do that is 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 students data for example
volume that is but because myself I'm speaking somewhat loudly only how about speaking somewhat loudly only how about others Guys,
speaking loudly only that but anyhow I'll increase and he said no I'll increase that. So now here so simply you can say that one now simply you can say what is the database in the sense method. So it's a
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
blah blah will go. But on that blahla the main categories are here. Now four one is storing, adding data to the table or adding data to the databases.
That is one thing. The second one which is very important one retrieving data from the large amount of data. Let's say that a banking system having one lakh
customers from that one lakh customers one customer information how to get 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
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 these these these are the operations that but these will be done in a different different ways. done in a different different ways. Okay. That is what okay. So now to work
on these databases. So so there are some rules in DBMS 14 rules is there. DBMS 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 dbase and fox pro dbase and fox pro anyone have you heard about those names
would be fine nice sounds good that so if it is because you guys are saying no no no yes that is because whenever the database systems are evaluated in the market in the past that 1980s or some '7s or something the very first database
'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 also 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 before that guys listen we We have a
types of DBMSs is there. See DBNS has been evaluated been 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
are in the 2026 we are in 2020 all about 50 years lifespan is there okay even if you go for 1980 so that is 20 years this is
for 1980 so that is 20 years this is again 26 45 years so this lifespan is there for these things but within this 45 years there is 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 you can say that what is meant by that relational database management system okay here so the relationship between 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 let me add two more students here Oh,
number is one that then exam is for example let's say that quarterly some 45 example let's say that quarterly some 45 and again 46 so okay now 46 that so 91 is the total so whose marks it is listen guys okay here the marks of the student
is not going to be entered directly because already some students list is there so the role number should come here any one of these four that is my role that is my rule here. This marks for the
that is my rule here. This marks for the uh what we can say that is 1,3 let's say that is so then second one quarterly okay now so then second one quarterly okay now like 60 here again 60 some 120 is there
this is 1,1 and you see this one guys for example let's say that here so I'm just giving that right now so in so I'm just giving that right now so in this way so 130 is the answer Suppose if
I give 1,09 is this role number is a valid is this role number is a valid here guys please tell me 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 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 system so
it is not possible so that everything have to be taken care by has to be taken have 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 these 14 rules that relationship concept is not there even if a situation comes that we have to maintain explicitly by the user itself. So under that these two
softares will work dbase and fox pro will work that is dbase and foxro will will work that is dbase and foxro will work but later on that that necessity has been recognized and the relational process the relationship process rules
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 regarding relation
process that is the main feature that is the main feature and some other aspects also there so the name has been given to the RDBMS relational database management management system we got a concepts
called primary key foreign key concept the foreign keys the concept has been introduced making relation between tables to avoid the inconsistent data or duplicate data or something else okay that we will come to know while working
on that. Okay. So that is the one here now RDBMS guys here now relational now RDBMS guys here now relational database management system
so I'll provide it everything that is okay you will come in the sessions that okay you will come in the sessions that no worries okay now just follow this one
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
in the sense It is perfectly suitable for the present It is perfectly suitable for the present trend. Okay, present trend that is how to communicate multiple databases which is located in a different servers.
how to communicate multiple databases which is located on a different servers that for example let's say that one more example I'll give you here uh yeah you take a banking system of multinational
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 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 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 object to class 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 MS 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 war DBMS.
Here now every tool comes under the RDBMS only like we have SQL server, you have Oracle, we have MySQL, Postgress 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 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
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.
center irrespective of the bank we go to that ATM we insert the card and we that ATM we insert the card and we withdraw the amount. Okay. But how for withdraw the amount. Okay. But how for example now I have ICA bank account but
I'm withdrawing amount from the SBI bank. How SBI bank will come to know bank. How SBI bank will come to know that okay he is the ICA bank customer and the transaction has to come from the ISA bank not from the SBA then he will
ISA bank not from the SBA then he will come to how it cost here yes so here come to how it cost here yes so here bankto bank communication will be there bankto bank communication will be there what that is bankto bank database
communications will exist bankto bank communication will though You are doing transaction from the SBI bank ATM. Okay. But your transaction bank ATM. Okay. But your transaction will happen only on the IC bank. Same
will 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 there. 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? database? 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 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 to repeat everything. It is one of the RDBMS database tool. RDBMS database RDBMS database tool. RDBMS database software tool you can write on that
software tool you can say that is here the current version of this one 8.0 0 is there. 8 is there now. And one thing guys you have [clears throat] to remember any database system you took
either Oracle or SQL server or MySQL or anything that there are two parts. There anything that there 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
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 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 differences between one software to another software that two 90%
same synt 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
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
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 sensor at command prompt sensor at command prompt permanent at command prompt that okay in that way it goes there. So SQL was using at command prompt later do on developed
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
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 poss even for all suppose for example you have a thought sir I want to develop my own SQL yes you can develop get the like license from the ANC company about that SQL then you get your own environment and you add
your additional features for And you can release yourself one SQL tool like that in it. So SQL is full of commands.
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. 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 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 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 come inside of that MySQL. So topic wise the commands has to be discussed. Topic wise 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 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 you see there here now what is a database? What is a database? See then as for MySQL collection of objects
as an an object is a data item. An object is data item in database. What 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 the 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
here. Let's see that guys how I'm writing here guys please follow that. So I'm taking now create space database space F 2026
space database space F 2026 DB I'm writing now and put semicolon at last okay every query ends with semicolon okay every query ends with semicolon here and one more thing guys here you
have to select that command that entire command has to be selected once it is selected you go This button is there. Flash symbol button is there.
Okay, see there now the first button the three buttons is there. Folder save and third button. This button is there. This button you just click on it. So once you click on that button on the bottom side you'll get one message and green color
you'll get 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 you click that administration if you go to here this one schemas once you get
this schemas here you have some schemas sis is there below of the sys this below sis is there below of the sys this below of the s right click on it right click on it go for refresh whenever you go for refresh the database
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
create database database name any name you can give aha have given Fab 2026 DB. you can give aha have given Fab 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 get like this and here database will be there. So below of that right click on
it and refresh the database what you created that will disable here. Guys, please confirm that. How many of you succeeded? 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?
patients. So once we settled on this environment then we will keep on uh working on the commands. Yeah, we already you already did that. Okay. So, can you uh decrease the screen
zooming? Can you decrease the screen zooming? Screen zoom. No. No. Maximize it first. Maximize it. No. No. Maximize it first. Maximize it. Maximize.
it's not getting maximize. So, go for control minus on your keyboard. Control minus on your keyboard. Hold on the control key. Click on the minus button. Control-y button on 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 wait don't do
anything just wait now there you are 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 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.
the command or f it's not an attribute here database name I shown you know that see there it is a database name see create database the database should be identified with one name database name
okay that is the name of database should be given be given I hope you got it right
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 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
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. Select that command and uh click on execute button. So that you see there
that database has been highlighted here and has been released like this tables, 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 the database which database you want to work. So then within that tables views
and all everything we have to work on it. So everyone is seeing the same results use 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 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 command okay let's see that guys Right
command okay let's see that 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 table name is what here now any table name should be given sorry
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. 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 role number is one
column name, student name is one column name, gender is one column name, age is one column name, location is one column name like this. So we have now some 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, again text has been entered, 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 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 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 ankle brackets and here I'm enclosing square bracket square bracket in the sense this part is optional. If required we can use if not
required we can avoid but whatever I enclosed within ankle brackets that must be given that must be used you can't 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 whatever you want you mention it and you open a bracket here round parentheses go for closing of that and put with semicolon.
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. 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 1,00 1 1,00 2 1 2 3 minus values - 10 minus 20 something 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 where car will be there here string with variable length I'll explain it this one just let me finish off these things then if I want to enter the date value date date so
date value only that while entering that date you have to go with first year then month then in this format you have to enter that
then time is there time values only time also having format same thing regular format hours minutes okay 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 image are in a database system.
If not image are 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 and audio format that should be converted into the binary
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 you forget about that. Okay, first we will get the knowledge on these first we will get the knowledge on these data types while creating table.
data types while creating table. Okay, that is so now so how to create a table by using this in a sense. So of course here we will be having one table course here we will be having one table creation. You see that guys here let's
take this is okay first of all let me go for explaining about one thing what is that you know okay anyhow you got this table here this command okay see there
table here this command okay see there now std name is there std name so in this std name see that is nothing but student name it 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 20 so what the difference between these two here both are entering for text only what is the
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
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 you need to tell. For example, let's say here I'm giving now my name. Let's say that Vayu Gopal I used. Let me know ma'am how many characters are there on
ma'am how many characters are there on that Vayu Gopal. Tell me how many characters are there on the way Gopal guys please respond. the way Gopal guys please respond. Nine is there. Okay fine it is nine.
Maximum how many characters we have given? Maximum how many characters we have given? 20. So how many characters are free space right now? How many characters free space 11 but okay fine that's okay here also I'm
okay fine that's okay here also I'm giving vo gopal here also I'm giving venu goal what makes the difference between these two you know in storage not explicitly listen carefully in storage in a database storage
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 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 where of 20 maximum you have given 20 characters but how many characters you utilize in this
nine character space utilized only nine character space will be occupied in a memory storage remaining 11 character space will left over for further usage further data story usage that is so for one more student for example if I
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
deleged for other perfection so that's why it is called as car and it is car fixed length it is it is variable length variable in the sense maximum 20
will not be exceeded. But how many characters you use based on that the memory will be occupied by the worker. But in a car whether you use 20 characters or not use 20 characters 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? Yes. Now I'm going to the here. Let's see that guys. Guys, you come back here
now. Now I'm working on which database? Feb 2026 DB. Yes. So within this I'm Feb 2026 DB. Yes. So within this I'm writing now create table here. Just see. writing now create table here. Just see. So 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 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 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 space 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 here
a std name. Then here I'm giving here web pair of 20 characters. That length is up to you. Okay. Then I'm giving here now birth 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 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 no right click on the tables go for refresh all. So you got a arrow mark here. Arrow mark in the sense one table is there. Click on the arrow mark. See you will see the
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 key 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 follow small letters, all you go
for 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.
a small letters also you see there. So whenever you come down here create table see that I'm writing create table emphy
name where care of some 20 or 30 characters then here I'm going for job okay where care of from 10. Okay, that is one thing. Then uh we can Okay, that is one thing. Then uh we can go for that salary it is also we can
make it float or else make it int only. No worries that even if it is float 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 there the small letters 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. whether writing small letters or capital letters anything is okay. So what is
letters anything is okay. So what is another one that here now? Wut again 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
executing queries and all that. So that is who already having knowledge So that is who already having knowledge be patients in a session. be patients in a session. So shall we go for
there I'll show you just wait okay first of all basic things we are working basic things [snorts] 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
can't go for all at once I'm taking slow by one by one one by one yeah guys is it okay sur [snorts] Yeah. So now we have created two tables. Okay. In a two different databases.
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 that 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 go for values val one val 2 something we have to be entered here.
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 up that okay that is one thing we have to go so let's see that how this insert command let's see that how this insert command has to get it that is see there now we
got one insert command for the buy data. So they insert into buy data. So how many columns are that column names and for that one values has to be given that is let's see now how I'm writing for this. So 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 data table in which DB feb is there. So that I'm writing like this right now.
Okay. So use of FAB 2026 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. Remember that mah here. 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 1,001. Put a comma. Then second
That is 1,001. 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 am writing that gita sahastra
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 past year then month birth date guys past year then month then day we should not forget that so
that here also single quotes so that I'm giving right now 20210 giving right now 20210 - 9 - 12
that is one thing then location that is also a string so that here we go for also a string so that here we go for that provid so entering data is not a case sensitive data data part you can give anything
that is I'm talking about only commands part capital letters or anything so data anything you can enter suppose here let's see that is I wanted to go for like this you see there what I'm doing you know first one uh so here Gita
okay sahastra will take no worries yeah so now select those two lines select those two lines and go for execute. Yes, it got executed.
Yes, it got executed. It got executed here that is but is that row is visible on the table for us in the sense it is not visualizing it is not visualizing here. So one thing so for inserting of rows we have used
one command insert that is okay but to see that data is there or not in the table in the sense again one more command has to be used okay so that is command has to be used okay so that is here now select command select you have
here now select command select you have to write like this select star from to write like this select star from biodata 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 this output should come here to you guys. Work on it that yeah see this two
seema the screen which you are seeing now. Try to execute that one. First go to your database where the B data table is there. Where the B 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. 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 date
in quotes two strings are there one date is there that is what the entire value should be enclosed within single quotes want to date format so here one thing moria always storage part like that only that you can't
change but for visibility perspection in a different formats we can view it for that one we have some functions but storage prospection you can't change that format but viewing purpose we can 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
let me know how many of you succeeded in setting of rows. One row it is.
Yes, even you use the select command also. I hope so that select star from bad 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 sensor 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'm writing a insert command
like this here. So what is that in the sense just wait uh uh yeah see there here so what I'm writing you know take this insert
command write down here only. 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. So then I'm writing here values. So 1,002 then here so pra
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 is that 1,00 to guys wanted I'm giving to show you
one uh mistake that is again already given role number so then here so here given role number so then here so here now I'm giving vanilla
here what is that in the sense my 2015 hyphen what is that in the sense my 2015 hyphen 10 - 10. So then again here something like I'm giving nellor okay that is one more location yes so
then one more I'm giving that here so here 1,04 I have given that so
three rows Yes, see there now I'm inserting three rows right now. At last you have to put semicolon. This is and I'm trying to execute this one. I'm trying to execute this. See that I
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
we're 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 one did a mistake that to show you this is because see the duplicate role number
has been given. It has been accepted but it should not accept. It should not accept that. Why? Why it is accepted in the sense? Because yes while creating the sense? Because yes while creating table while creating table we haven't
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
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 do it that how to write multiple rows here by seeing that you just see that m Arun joti What is that? If I use car type then need to mention the length
also of course. So whether it is a car or car length must be mentioned. Now that is compulsory. I told you know that you see here you see here see joti see this excel sheet the same
thing the column specification is same 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 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 has to create uh 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 how to work with the constraints and all that is let's see now here actually we
go for like this here one thing so what is that you know let's make it like student is the table name okay here a role number is the column let's see that
is here now the student name is another column like is another column is column column like is another column is column is there then one more is there like h gender is another column Okay. Then the location is another
column we have like this. Suppose five columns is there. For this it is a data columns is there. For this it is a data type is int. And for this here where car of some 20 or something it is in it is car of one only. I want to
in it is car of one only. I want to represent gender male or female Mr. F. represent gender male or female Mr. F. Then here it is where car of 10. But on these columns I wanted to put some restrictions
that means in the sense some rules. What is that you know? Okay here it should not be duplicate and uh should not be
what? Null empty. That is one thing we have to go. Student That is one thing we have to go. Student name it should not be name it should not be null. That is one thing. And uh it
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 concept called constraints is there there is a concept called constraints is there Okay. 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 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 constant 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 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'll get student name should not be null in the sense here it should not be null in the sense here it should go to not null. What that is not should go to not null. What that is not null is see it should be in between 20
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
constants 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 down 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 h. So I'm writing guys here now see there create table student is the table
there create table student is the table name. Okay then here go for role number it is into now you specify the constraint after data type constraint has to be specified. If we give the primary key that will not allow you
duplicate values and null values. Okay that is the second one. What is Okay that is the second one. What is that std name? Okay. Where care of 20
that std 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. If any other alphabet you have given it should not accept. Then
you have to write a check constraint like this. What is that? You know check gender is equal to column name. This is the
column name I'm giving equal to in a single quotes. [snorts] Okay. M single quotes. [snorts] Okay. M or 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 so is it is what here
down. Okay then is so is it is what here int. Then again now for this also check int. Then again now for this also check check is greater than or equal to 20 and check is greater than or equal to 20 and is less than or equal to 30.
Yes. Then location. Then location. So it is work of 10 or 20. So default So it is work of 10 or 20. So default I'm giving that hydroad.
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 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
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 constraints 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
hydrobat 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 column remaining columns you can addit that is suppose if the student location
is not hydrobat then you add that location column and insert row. So now see there here I'm writing now insert command insert into student. So I'm
command insert into student. So I'm giving role number then std name then giving role number then std name then gender then age that's it location I'm 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 select star from student guys this select command has got so much of
explanation but as of now we are using like that tomorrow we go for complete discussion about that is yes we got the data you see there actually in insert command in insert command we haven't added location but we
got the location hydrobat because while creating table we made that hydrobat. creating table we made that hydrobat. Okay, that is okay guys. Now see without 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 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 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 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 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 changing the value here you can know 1,002 now you see there I'm checking the
uh Here let's let me change the name. Now it is morally and it is a male person but accidentally I have given Y but we have
given check constraint either M or F. Okay. So select this and go for execute once again. See there row is not once again 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 While entering data, while inserting rows that proper data should be maintained here. If any uh like data is
invalid, 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
Yeah, no worries. That suppose that means so suppose another person having another a different location right you are asking more yeah
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 hydroad 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 okay female okay 21 let it be that okay here so now you go for here now you execute this one where yes this row has been inserted and Here uh
go for this is there. See the third student will come here. That is 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 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
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 your MySQL workbench on local machine and you create one database
how we have done yesterday in the same way you create one database. way you create one database. create one database here that is so now create one database here that is so now here I'm creating one database create a
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 created no worries I'm creating web 2026 web 20 26 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 fab you will be having that fab yes 2026 DB is there here so now go with
what like use command to access into that. So this is the one thing just we took. Now no tables are there because it's a local one. Okay, it's a fresh database. No tables are there. Now we will create
No tables are there. Now we will create the tables. We will create the tables. the tables. We will create the tables. So now I'm going to uh discuss some other commands. So, so already discussed commands is
So, so already discussed commands is what that sees c 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. Then inserting rows has been done. Right. These are the things guys 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 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 drop command. So then here discuss. Then drop command. So then here alter command. Okay. Then describe command. Okay. These are the commands. After
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 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 discussion Shall we go for with the first update command mark?
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 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 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 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.
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 I'm creating. So create table marks. Mark 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 S name is the column name. Then I'm taking workar of sum 20. Okay that is one thing. then
sum 20. Okay that is one thing. then exam I'm taking and where 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
uh constraints also what is that here you apply it should not be null and it it also should not be null like this then afterwards guys you see here that u subjects I'm giving so I'm giving column names like subject one only it is int
&gt;&gt; [clears throat] &gt;&gt; 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 avg it's a
then I'm giving percentage avg it's a decimal float and I'm giving here result it's a car of Four. What is this result in the sense? Pass fail. P A S S four letters. F 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
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 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
you are going to insert the rows row insertion. So while inserting the rows insertion. So while inserting the rows avoid this total, average and result because this total should be calculate and average also should be calculate and
result also should be calculate. Okay. Past fail that is. Past 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. Now 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. So one. So here uh sur is the name. Then here
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 same. I'm changing the marks
here. Now 27 it is 59 and 77 59. 59. 59 or also 49
59 or also 49 and this is okay here row 29 and this is okay here row 29 okay 90 and it is 80 yes guys see guys here like we are we took this one and I'm executing this yes five rows are see
I'm executing this yes five rows are see five rows are added so that is one thing five rows are added so that is one thing we are getting let's see the data select we are getting let's see the data select start from what that is marks can do it.
start from what that is marks can do it. Oh 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 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 plus subject two. Subject one plus subject two. See that guys I'm writing max 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 automatically goes to that subject one column and
subject two column. It respective row values will fetch and that updates onto the total column. all the rows not only five 500 5,000 it doesn't matter it will be updates over there to 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 ing 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 one I'm doing some expression here calculation the result will be updates on 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 guys. 156 86 126 96 170 something got it
guys. 156 86 126 96 170 something got it or not? Guys is this query is understood by everyone.
Is that update queries 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 much here. Now subject 1 + subject 2 by 2 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 zero why can't you go for two 2.0 zero why can't you go for two actually one thing guys uh here data
type require things will be fx here now so subject one data type is int subject two data type is also int subject two data type type is also int subject two data type is also int if I go with like this two
is also int if I go with like this two 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
For example, if the average goes here like 57.5 something as came that 0.5 will not be written here. Now if it is goes like this and but if it is going for 2.0 this is part this is everything integer
and this is the float. So integer with float integer with float you do any 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
okay but if you have such kind of values that also will come this is there two update commands guys listen these two update command. So updating all the
rows of the particular column all the rows. But right now see that guys here one point what is that you know result
point what is that you know result uh should 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 calculations directly value pass. Suppose if I execute this one 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
If every subject if every subject marks having 35 or above 35. But you see here now the first first student Sur 67 89 okay pass will come.
Second student Romesh is there. See subject one having 27 and second subject 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. Then 7749.
go for empty only. Then 7749. Okay filled. Then 6729. No. Second subject is fail marks is there. No pass there. 80 and 90. Okay. there. No pass there. 80 and 90. Okay. Fine. This is what. So here how to
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 to specify it here. Now how that condition can specify? Yes
how that condition can specify? Yes guys. So here where clause is there. guys. So here where clause is there. Okay. So note you can take down here Okay. So note you can take down here where clause is used to
specify what it 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 name subject column names here. Subject one, yes, greater
names here. Subject one, yes, greater than or equal to 35 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 to another condition and in the sense all conditions must be satisfied. All all conditions must be satisfied
should go like that. That's the quiling. Now let's see we have a five rows. Okay only three rows has to get update. See there. Now wherever the conditions are satisfied see three rows are affected is there.
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 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 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 mode something like that is giving yes it's an update command is that yeah
fine no worries before executing the update command guys so just one second update and delete command directly won't work so for that one command has to be
work so for that one command has to be used here I forgot that set update in MySQL so one simple command is there. Just wait. Yeah.
update command, this command you execute first, write on set 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 solution I'll give you. First you do this one. First you do this is because of avoiding unnecessary updations and deletions directly. It won't allow you to update the rows.
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
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 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 one or two will it work or not? See that whoever having a
sharing sorry this updating now see 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 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 query guys here now change the student name as like Chandra Babu
uh who are having a role number who are having the role number is here uh like having the role number is here uh like five. Now tell me the query here. Now whose role number is five? That is for that question. What is the answer? I
that question. What is the answer? I wanted to change name as a Chandra Babu wanted to change name as a Chandra Babu whose role number having five. That means so this Narish name I wanted to change as a Chandra Babu. How that can
change as a Chandra Babu. How that can be done? has given answer and what about others guys please
into the double quotations nav 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 chundrau also it should be single
for chundrau also it should be single quotes not the double codes. 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 of yes name is equal So update mark set of yes name is equal to chandra babu to chandra babu where role number is equal to5
where role number is equal to5 you should get like that. execute it that's about update command guys. So how
the updations will be done here? How the updations will be done? That is one thing we are having.
and 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. See that guys here. Let me going for like this. But of course just see there.
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 Table will be there but no data will exist. No rows are existed. 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 let I'm executing
this. I'm not executing the first one because deleting rows. No, if you delete have to insert and update everything has to be go for that. Yes, I executed. You 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 command I'm giving that you go with this command I'm giving delete command here go with this Try to execute that command. Don't delete all the rows.
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 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. 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 example. So how can we go for adding a new column So how can we go for adding a new column that is one thing or else so I wanted to
delete existing column row deleting in the sense delete command is there row deletion just now we have seen delete command is there how to 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
are there. Let me write on that. So here so this command is used to perform so this command is used to perform different uh uh modify operations. different uh uh modify operations. Modify operations on table structure
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 one thing. So what is its syntax in the sensma alter table table name
and here we have some keywords is there add modify drop here we have to write on expression. command. What are the actions we do here? You
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
one here renaming renaming existing column names. existing columns. These are the different six operations we can perform
by using of alter statement. Yes, to do this workma what we do right 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 implement these all while creating table we we will made it some mistakes. Okay, that how can we correct it? Because of that I'm creating a one more table here.
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 it is in and I'm giving the not null. I'm not giving wantedly primary key. I'm not giving wantedly primary key. Okay, not null later we add that then
yes name where care of sum 20 where care of sum 20 or else do it like this car of 20 or else do it like this car of 20 so that is one thing now let's see here
then uh yeah city 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 this is the marks table. This is the sample table. There is no data. Okay.
These are the columns which we are having that. 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
there. What is the describe command? You know that No no no no. It allows to show
structure of table. Structure of table that. So how this can be taken up in the sense a very simple command it is. So 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 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 those all will come into the here. Now see there now field type not null or null can any keys are there primary key foreign key something. Have you set any
default value or not? If anything is there for any column default that will be shows here something will come that you want to see that see there describe you want to see that see there describe even de 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
so no no null remaining all or yes yes has been given that is like this you can has been given that is like this you can see what we can call structure of table okay fine now we are focusing on this now let's see one by one I'm taking mat
add a new columns add new column so by name phone number what my 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 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 where carara 10 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 like that only. Simple command it is.
Yes ma'am. So column has been added that. But how can I see that? You have that. But how can I see that? You have to check with the describe only. to check with the describe only. See own has been added here.
Same command one more time you repeat it. it. So here uh what is that pin code? Pin So here uh what is that pin code? Pin code is six digits pin code. Yes, six.
code is six digits pin code. Yes, six. I'm adding that also. Yes, you see there that here. Yes, pin code like see there the columns Yes, pin code like see there the columns are extending here. Now
that is what in this way you can add the columns. You can add the columns here. This is one command we are having. 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. 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 now expression in I have written here now expression in the syntax because every aspect is going the syntax because every aspect is going to be different uh writing of command
to be different uh writing of command drop column column name drop column column name phone column I don't want yes 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 is one thing here we are checking
then one more so here you see that whenever you scroll down I want to change the data type of this s name column 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 column to where of 20 something like this
to where of 20 something like this with what we can call mother 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
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 of sum 20 then not
give it where car of 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
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 a work car 20 and null become as not
a work car 20 and 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
structure that is then we haven't added the primary key here. So add one more 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 s name column. Sorry. primary key? Yes s name column. Sorry. Yes.
So that you just see this one here. Yes, it has [snorts] 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 key is not there for a table so you are adding a new primary key that is
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
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 drop what mother 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 the primary key to which column it is there for that it will be deleted
Okay. So how can we see that whether it is deleted or not? 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 s name
name into std name I think so that is
what happened to this is rename column yes name
see this one here. So now the column name becomes here. Now std name. See name becomes here. Now std name. See there.
So like this. So alter command works. Now this is the way the alter command works over here. That is then the next one we'll go for here. Now there are two commands is there. The very first command is right now drop.
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
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 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 further 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 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
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 table will be deleted. Entire table will be deleted here including data. Table deletion is this including data it will be deleted. That is one thing. And
one more point here. So whenever you delete some rows in a So whenever you delete some rows in a table, whenever we delete some rows in a table, whenever we delete some rows in a table, one sir, how to get started with
MySQL workbench? In case practice lab is not working. Okay prai just I'll take to your screen just wait for a few minutes. So here
whenever you delete the delete the rows you have a chance to get back those rows deleted rows once again we already discussed 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 table that we cannot get back that we cannot get back here
so that drop command and 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 reverted that here. Okay, that is one thing. There's a simple command drop. There is no any wear clauses or something else there here. I'm not deleting any dropping any
table right now. Of course, if you want, you can drop table here. You see there the sample table will drop it here. You just go for drop table sample.
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. That is called here now truncate. there. Truncate command mises this command
Truncate command mises this command is used to delete all rows from table. is used to delete all rows from table. All rows from table.
This one you go for that truncate table table name table table name like this you should get that it want to delete the table. Listen carefully. It want to delete the table.
carefully. It want to delete the table. It will delete all rows from table. It will delete all rows from table. Delete all rows from table here. Now what makes the difference between delete and truncate?
and truncate? So delete and truncate the difference 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 with what? 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
these two points you have to remember that is that is these two points has to be remembered delete all rows. If you skip the where clause you can delete all rows that okay
and whereas truncate always going for all rows deletion. Trunket is going for all rows deletion that is and you have a 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 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
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 a trunate what that is truncate command is there
So I hope that these all commands are completed right. No need to practice drop and truncate. Just remember that is whenever it is required then you can go.
I hope that the difference between a drop delete to truncate understood or 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 select Select. So because select command how to retrieve data from table and all that we'll go for it right now. Let's see that is how it works. Okay. The next command is guys here select command has
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 the select command we need proper data. We need proper data that is so as I said
beginning of today's session okay everyone has been downloaded that SQL data sets right downloaded now downloaded now 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 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 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. data. Now this data we wanted to bring it on
Now this data we wanted to bring it on to our database right now so what I'm doing right now you know here
uh what you say that is so this is what type of file in the sensema it is CSV file you just see there that is so see there Microsoft excel comma separated 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 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 to our database from
CSV file to here from CSV file to here we wanted to import that so now I'm performing one small task just follow everyone and try to do it that you have to
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
Right click on tables. You will get an option called table data import wizard. Table data import vizard is there. You click on this. So you'll get to 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.
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 EMP that is remove the table. Just keep EMP that is remove the table. Just keep the emp
see it is showing entire data here columns and its data everything it is showing to you here now the data is coming right now there then next okay simply go for next and next next go
for finish that's it ma the table will have here just see that go for refresh see you have a EMP table.
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 vizard the table data import vizard table data import vizard and here it will be empty actually go to the browse and where that file is there that you go
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 is 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?
that EMP table, right? Everyone imported EMP table from CSV 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. [snorts] So 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 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 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 in this employee table guys you see the data so first of all we'll talk about
this employee table have we 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 ID like you are having. So you see here employee E260 is the
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 ID 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 that employee. 583. Yes. See there. So Janet Halle female can she's a manager.
Manager for what? Retail department. See so Joy Collings is an employee of senior set scientist working in retail department. For the department Janet Halle is the manager. Okay. 14 years of experience living in
Colombia South America taking 10,000 salary. Okay. Now employee rating is salary. Okay. Now employee rating is two. But she is the manager but she's two. But she is the manager but she's also having next reporting manager. Who
is that E002? Who is that E002? You have to search Who is that E002? 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. She president also has to report to someone who is that E001. Who is E001?
someone who is that E001. Who is E001? Arur black male CEO of the company. Okay. 20 years of experience USA and North America. So CEO is not going to report any other person. Himself is a reporting person. That is one thing.
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
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
somebody having some already knowledge no for those people I'm asking that 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 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 of in the place of star you define the column names that's it define
the column names over there so how you go for that is here select So I want first name so that first underscore name underscore name then comma so then job is there ro then
dt okay then salary so from 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 that 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 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 year 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 selective so first underscore name I'm taking only first name that so then ro
then salary so that is fine here then here you can so that is fine here then here you can write like this salary into 12 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
salary and yearly salary is there but this column is not present remember that table don't think that it will be present on the table no for temporarily 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 one but I don't want like that what I want in the sense of I want there a heading yearly sal
something something I want that okay how that process can take here so okay how that process can take here so that we are having here column aliases 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 or 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 specify that is see how you write a query for that mark see there now I'm taking right now there
select first name as a EMP name. See, I'm first name as a EMP name. See, I'm changing the name of that role as a
designation. Then salary as a monthly cell. 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 amp name role has been taken as a designation salary 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
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 that's called as an alias column aliases what is that column aliases you can write down 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 it's getting required columns whatever you want the specified columns okay whatever it is there then if you
want to perform any calculations that just see there the calculations you can 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 ro as a designation salary as a monthly sal something like this. So this is the one just we do then So this is the one just we do then simple one part of expression section.
So then here one more also there guys right now there is a clause called 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. 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 now. sorry like four is there right 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. COE is 7 like you have that and even if you come to in the department column you have a deep uh retail department one department finance
is another department automotive is another department healthcare is another department 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
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 dpt 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?
All values of that DP column will come. Right? Only one column DP will come. All Right? Only one column DP will come. All 20 values will come. Right? All 20 values will come there. See there. Let me execute that.
that is okay this is one but I don't want that duplicates I want actual values very simple thing that just add a distinct
simple thing that just add a distinct dB from emp now now what will happen you know only actual values will come see it eliminates all the uh duplicates and shows actual values only for showing
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 distinct gender
okay so gender from emp group in the sense what chendra I'm not getting your point gender spelling mistake
that. You wait for group I clause in order not started 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 EMP actual countries will be from EMP 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 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 what in this expression area we got the 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
alias names that is second one. Third one is providing distinct clause to see one is providing distinct clause to see actual values from specific columns. Okay, that is one thing we are having. These are the things 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 weight 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 employees are working in India three employees are working in India
that is okay 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 requir is correct but no value male okay
requir is correct but no value male okay m
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 like employees in uh what we can call that retail
in uh what we can call that retail department. Retail department that is department. Retail department that is one more. Okay. This is one thing. These are the simple conditions that is guys here. Now then show all employees. So
whose experience is so greater than or equal experience is so greater than or equal to 8 whose experience is greater than or equal to 8 like this. Now see there now we have given conditions on only one
column. All these queries are having one column conditions. Suppose if you have 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
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 see the query can get like this here. Now select start from EM
here. Now select start from EM where see that guys salary greater than 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. between 8 to 10,000. Yes, we have a like five to five employees are there here. Let's see their salaries here. So 9,85 9,500 10,85
is there. 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 8,000 salary no employees are there. Okay. You do one thing that you can you can check that see already 10,000 is included 10,000 is included like that 8,000 also will be included.
and one more is there here. Let's see that guys. Uh yes, one more query here. that guys. Uh yes, one more query here. Show all employees uh in uh retail and automotive
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
is equals to retail R DBT is equal to automotive. You need to write like this here again one column with R operator.
So that is and in the sense in same column in same here both retail and automative 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
trying to explain here. If you keep the under, see what happens there. under, see what happens there. No rows will come
now we are getting that here. See there. 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 you got my point ward
Wink 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 of like this. What is that? You know in of 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.
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 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. Now 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. 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 employees. So all female employees whose salary
between whose salary between uh so something like 5,000 to 10,000 something like 5,000 to 10,000 yes guys will you write the answer for
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.
Yes ma'am. 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 down that salary between what that is 5,000 and 10,000 we should go for like this here that see all female employees see their
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.
go for it right now. There. Yeah. Shall we go for one more? M 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 is 5k to 10k. Now can you make the query for this?
Can you make the query for this here that values you can replace it to either any other values like 5k to 10k or 10k to 15k or something else you can make it 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 what that is retail
or autootive. Okay. under 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 whose experience between uh employees whose experience between uh like what we can say that 5 to 10 years.
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
you practice to make you practice that is
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
employees. No. So then five years show only first name only first name and uh experience column and experience columns. Then if I write like this what is the
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 right now what that is select first name
experience from emp have experience between from emp have experience between five and 10. You are going to write it down over there. How many employees are there? Six
How many employees are there? Six employees are there. See 7 years, 6 years, 8 years, 9 years. Something like we are having. That is
senior data scientists what mother is senior data scientist so any spelling method scientist from
any spelling method scientist from retail department. Uh right on now retail department. Uh right on now show all senior data scientist. Can I hear that is use only
first name first name, first name, uh what that is job and department 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 id
getting any right uh 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 still we have some more questions are there. But okay let me go per 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. 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
that means you you are asking like who who is whose manager right who is whose manager that is Yeah, for that we have to write a self join. Self join has to be right. That is joins
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. No like operator. like operator. So this is used to specify
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. Group of characters that then another one is underscore.
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 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 or 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.
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. 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 is ends with for example let's
with what is ends with for example let's say that some a last letter is a so say that some a last letter is a so percentage a should write here now percentage A should write it over there. Yeah. Katrina, Diana, Cynthia just see
there three employees are there. All the 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 that is exist alphabet exist exist in the sense now anywhere exist in the sense whether in first
position in last position in the middle anywhere if you go on that then anywhere if you go on that then percentage a percentage will go okay that is then go for clicking on this one almost 13 employees are there
this one almost 13 employees are there 20 out of 20 employees 13 employees names are having alphabet A in their names it might be in a different names it might be in a different position see Neil is there Katran Karan
position see 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 letter is A and remaining all having some in the middle.
So like this we can go this percentage symbol that depends. But actually here if 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 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 characters should ignore
that you can take down Here see that stick the second letter of every stick the second letter of every employee here. So Janet, Patrick, David, Karan, 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 1ore 2 3 4 now tell me what ignoring yes 1ore 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 uh Name should starts with uh name should starts with what mother is alphabet. Yes,
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
what we can call that is so queries on where clause what my is where queries on where clause what my is where clause partner 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 letters letters ignore it. Now write the query for this.
which has a employees which has a as the last character in
select like underscore A and 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 swag your query will check only four letters
letters which is having second letter E and fourth letter E. You should keep the and fourth letter E. You should keep the percentage symbol after E
A_E then percentage should go 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 pachchi 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 come Now we have to discuss about these two group by clause and having clause has to go. So for that first of all you have to work on group functions to work with the 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 I'll speak with you at the end of session these group functions for the this these functions are used to perform different
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, count
max, min, sum, average, count, count count, star something is there. Let's see now how these functions will work first. work first. So whenever you come down here
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
value from the salary column that's See 16,500 is the maximum salary
So let's see that is right now there what that is find max cell
comma sum cell. Okay. Then average sal average sal okay 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 salary they are taking there is a salary salary they are taking there is a function called sum added that salary so then here on average how much of
salary they taking every employee have avg salary from emp that's it
here see that the maximum salary is 16,500 see that the maximum salary is 16,500 the minimum salary is 28 800 totally the minimum salary is 28 800 totally they are taking 1 lak 56,800
salary and on an average they are taking 7,815 rupees every employee. 7,815 rupees every employee. Okay. So like this we can go.
So with this functions this is only we can do it here. can do it here. And one more see there here And one more see there here suppose count the
suppose count the um count all employees. So in a table in a table that how you go for that is select
count of star from M. So how many rows are there? That is 20. 20 rows 20 employees that every row
having one employee detail 20 rows 20 employees. And at the same time if you want you can count the number of if you want you can count the 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 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. 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. What that is null values will not be counted.
will not be counted. So that is one thing you are. 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 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 what will happen will I get will we get that name see showing an error
in aggregated query without group by clause cannot be used that means guys clause cannot be used that means guys here No column should be used directly.
No column should be used directly if you're not using the group by clause. 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 functions we have a clause is there group by clause group by clause is there what is that group by clause see that this clause is
used to divide a column into group subgroups subgroups subgroups based on other columns based 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 dma here now show max salaries based on each department I write like
this so select max of salary from EM in the 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. from EMP. So here you have to write down group by So here you have to write down group by what is that? DBD.
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
whatever the listen guys whatever the column name you use in group by clause. Whatever the column name you use in group by clause that column name you can mention here. Yes, like this we can do.
Now see then h retail department maximum salary is 10,000 finance department maximum salary is 10,500
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 that? Yeah. Group by country.
India the maximum salary taking is right now 8,500 minimum is 3,000 totally 18,500 is there 6,166 is the average salary like we have a China okay only
one employee is there I think so so that maximum Colombia India. Okay. USA that is Germany and France 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 okay so Now that process can take here.
from emp group by what that is group by ro can go group by what that is group by ro 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
okay. 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 group by clause. Listen carefully guys.
Group by clause is completely depends on the group functions individually also we can use it but most of the time group by clause can take on group functions only on group functions of chandra praash I think so you have
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 the two groups the distinct clause also we did that. Now okay this is group I 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
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 uh this one? Where clause? What is the purpose of wear clause guys?
purpose of wear clause? Why we are using where 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 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 by is not there, no having clause used. But having group by clause works without having clause. But having clause cannot be used group by clause. 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
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 sales [snorts] 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
greater than or equal to 10,000 you should write like this see I'm should write like this see I'm specifying condition on group function group function this is the condition is happening on function not on the column
happening on function not on the column if it is a column we use where clause if it This column we use where clause but here it is [clears throat] the function so that having clause but having clause can't be used without
having clause can't be used without group by okay that is now you see there group by okay that is now you see there here see healthcare has been removed here see healthcare has been removed that means filtered that
the same thing just see there here uh what is that uh show uh number of employees in each
department whose counter is greater than or equal to five. greater than or equals to five. That is greater than or equals to five. That is then how can we write that having
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.
So make it that here it is four and this is also going for four. Okay. So like this you are having that is about the having clause. That is about what here having clause is
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
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 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 claus is You can also include the where claus is 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 salaries maximum max salaries what that is min salaries
of retail under 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 clause select what that is DBT max of salary
comma Min of salary okay from EMP group by DD 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 wear clause should come always after
from. This is the order only you have to follow. You see this is this only. This is the only order we are having
where group by having that is like that you will be getting that. So here see you will be getting that. So here see even see there if I write like this where it shows and see where it is showing that DBT in
retail comma then what that is finance showing an error that see it is not allowing you write the where clause after the group by so take this
so take this after this you I know.
Guys are you able to follow or not? Understood this query.
so I'm taking to you last clause of the select statement ma that is here select statement ma that is here order by clause.
simple clause. See there order by clause. This clause is used to show rows either in ascending or descending rows either in ascending or descending order of specific column.
This clause is last clause of select statement. Okay, that is so order by column name or de S something we need to get that is
de S 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 or 3,000 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 an ascending so that if other clauses are including order by clause has to move to the last it has to move on to the last
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, that is finance
should come. But it is not coming. No worries. Take this query here. worries. Take this query here. Okay. Right now,
Then what should you write ma here? You have to write order by dbd. Now see this result ma retail finance. Now finance and retail should come
first 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. H here and apply order by clause. Order by first name D S C descending
order. If you want descending order, descending order will come here. That is on first name I did that. See first it starts with Williams and
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
multiple columns in the sense okay we do like this. like this. So
show all employees 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 we have to that in that case of comma we have to write first name first sorting will done on country after that then first name now see this result
Diana Chad Emilich Cynthia is 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 there. This is the Colombia part. Yeah. Smith.
If 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
guys is it clear or not please let me know. Yes. Yes. Mr. Naven is it clear? Naven Ra.
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 case statement that [clears throat] is one
creating table using relationship [clears throat] [clears throat] primary key foreign key process. The 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
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
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 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
call it as a subquery. As for that, as for that process guys here, one select command we are going to write to another select command. one select command we are writing on into some other select command that okay
so that is one thing we can do as of now okay now let's see that if [snorts] so 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 I 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 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 like
aggregate functions or group functions. Aggregate functions are group functions. Aggregate functions are group functions. See there now here select max of sal
taking that is an EMP table you are finding the only maximum salary here suppose sir if you ask like sir can I get what who are the what we can call that employee who is getting that
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.
got right now? 16,500. Okay, that here now. So, select the star Okay, that here now. So, select the star 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. See? So is Arthur block CEO is the role of that person. So getting now 60,500 is the salary maximum salary. Okay. Fine. Okay. Like today he is getting 16,500 is the
salary. After few days he might get salary hike. It may go to 1 lak 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 so we need to write two steps two select statements two select statements so can't I get this entire thing in one statement whatever it is the maximum salary doesn't matter
whatever it is the maximum salary doesn't matter it has to show all the 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 by writing another query
by writing another query to get the maximum salary. That means can I write maximum salary. That 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 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 lowest sal. So can we go for like this
here? Now what is that mean minimum is the function is there that aggregate function I'm utilizing right now that 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 subquery Must return
rules here see a subquery Must return see must return single value or single see must return single value or single column values. Single value or single column values. Single value or single column values. So based on operator used
that is the first one which means actually from this one just from this where subquery in the sensema the subquery goes like this here. What is that? So this is what a subquery
has to be written in where class of main query better you make uh this one into query better you make uh 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 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 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
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 subquery will get executed 2800 will come here now see that 2,800 then main
equal to 2,000 800 the minimum salary employee details will come that is okay and that one thing this subquery
must be written single value one value must be written single value one value or else one column values that two based or else one column values that two based on the operator the operator what we
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 greater than or equal in the sense. Please tell me that guys if you are using greater than
less than equals not equal in the sense so how many values it will take so how many values it will take how many values it will take guys for a condition how many values it will take
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
it will take that is just a multiple values that is what here now in operator in the sense multiple values that is okay multiple
multiple values that is okay multiple values commas operated that two on multiple values of multiple columns are single column multiple values of multiple columns are single column
single column that you tell me guys guess it single column only that point you have to remember to remember okay see where salary in for example
okay see where salary in for example 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
multiple values of single column that is the point here we I'm mentioning here. Now see there you can that is 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 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 it
value or single column values that's it that point you keep in mind okay that is my here now see there with the subquery s we got it here this 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
from emp tricky here where salary less than less than within this again going for selector 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
How we are getting that? So analyze this query. What is the operator we used query. What is the operator we used here? What less than first this is what here? What less than first this is what you get there. Listen carefully. Select
max salary from EMP in the sense. So you get 16,500 only that is subquery is written 16,500. Now you understood select max salary Now you understood select max salary from EM where salary less than 16,500
from EM where salary less than 16,500 which means that it is finding maximum 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 with a purely operator. You see that if I go for now less than or equals to then there is no meaning for that one 16,500 only you'll get so
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 yama so show an employee details
second maximum cell now you 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 another subquery you can write down sir
subquery for this see ward I'll show you now just wait first [snorts] let me finish this that's what I'm telling right now ward the same the situation will come here show second sorry employee details who
show second sorry employee details who is getting second max okay so then can 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 data I'm adding here that I'm adding see that 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 this will get executed. Okay. 16,500 will come. Then continuation to this
will come. 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 that here guys understood or not this quy
understood or not ma guys please let me Of
can't go for more than maximum salary. No anything whatever you have to do that ex up to maximum salary only the same thing we are doing here your calculation is correct whatever you are thinking that's correct here
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
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
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 where salary [snorts] where salary [snorts] again going for less than what that max
again going for less than what that max of salary from EMP of salary from EMP we are taking like Obviously you get third maximum salaries 11,000 is there
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 So here if I write like this show an employee details
who is getting max and min salaries. How we write a query for this?
How we write a query for this ma? I want an employee details who is in one query I want maximum salary employee and minimum salary employees both two minimum salary employees both two employees I want right now
two employees we can write on over there shall we go for like this select shall we go for like this select star from can anyone try that please try
I want an employee details So he's getting match and min
I'm giving one clue also. You have to use in operator
Ma'am, should I write
Exactly current that's it that is the answer we get see current has given mark current has given answer that see there select start 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 comma from EMP that is one query. Put a comma then you go for that here. Select max of salary. Max of salary. behind
from EMP. Okay. Then this is done. 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 subqueries. salary. This will give you maximum salary that works on in the inoperator. 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 soaries brackets part it's not I think so optional guys here it's optional guys here it's no no see actually here it won't allow
you here comma after completion of query semicolon will allow you. So that is what see it is not allowing you to write a second one because of that if you keep that parenthesis. Okay, now it works there.
Got it? Navindra Navin Raj sorry. Yama. 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 so
who are having who are who have above the max salary
above the max salary above the max salaries above the max salaries of what we can call retail department of what we can call retail department I want like this now
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
So first find 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 EM see there now here where what from EM see there now here where what that is. So, DBT is equal to what we can that is. So, DBT is equal to what we can call retail. Shall we get like this?
That will give you the see that 10,000 we got it that. So then simply you go for that here. Select star from EMB where okay salary
Select star from EMB where okay salary in 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
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. usually we write like this. group by what that is d I'm writing so
all department maximum salaries will come 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 yeah see there that is also also fine
that is also also fine this also you can add it here this is also works here now or both will give you the same answer
that without group by clause with the group by clause we have been solved that there are different ways to write a query is 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. 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? So 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 uh let me save this one like a subqueries 21st February like a subqueries 21st February 20 subqueries. 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 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 So, so for that I'll show you one thing that 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 here.
Okay. Ro number, student name like we go for gender. So now here city something we have something like this here. 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 what that is three students are there here so now I'm taking that
marks is one table you are having in this marks table. So what I'm taking you this marks table. So what I'm taking you know serial number exam subject one know serial number exam subject one marks subject two marks total and here
marks subject two marks total and here I'm giving role number something like u so what you say that here so quarterly is an exam
here so quarterly is an exam okay some 56 78 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 to go suppose let's say that 1,002 okay then here this is so so what we go 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 offer 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 here now let's say that 130 whose marks it is suppose let us say that if I add 1,4 it should not accept here
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 are there in the by data table. 101 role
number 10, 102,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 it should not happen on a data basis so that we have to make a relationship
so that we have to make a 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 key it is simple wayma that is it is a primary key
and here it is a what in the sensema here foreign key
primary key and foreign key will go per 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 uh like you can s 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. So there are a different ways that here now see I'll show you now there now see I'll show you now there you delete this one here just see that
so the relationships you can take in four ways what that is the relationship you can take it in now four ways that here first relationship is one to one is there now
what that is one to one relationship will be 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 relationship?
here see there now one to one relationship
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 is this is one just we can take down here 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? Sales report. Just see there sales report consist what that is consist of customer and product details
product details product details here here this you can make sales and this is going for whatma here let's and this is going for whatma here let's consider so that customer and it this
you can take whatma that is product something you can and you can make reverse Also many to one also going for same thing that what mother is many to same thing that what mother is many to one also you can make that is
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
next fourth one. Here that is many to many. 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 can get B to D you can get and B to D also you can get this is called here now also you can get this is called here now many to many what that is many to many
you can take that is here now many many to many relationship that what is an example for this many to many guys can anyone guess for that 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
multiple employees. So multiple projects. Okay, that is multiple employees, multiple projects. You can take that is multiple projects. You can take that is and a simple scenario method.
What method is multiple employees, multiple products. Okay, that you can take down here.
So this is the way just you can take down that right now. Yeah, this is the one kind of many to many that is. So this how can you make that? How can you make the relation here now in a data in the form of database tables
there goes a primary key and foreign key concept. Let's go for working on that primary key foreign key concept by creating some tables here. So here
so what is that you know see how the parent sorry for primary key foreign key we'll go for here now when it coming to this one guys see parent and child 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 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 a primary parent table. Okay. So then this should be a primary key
in a child table. Common column will have foreign key. Obviously that common 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 has been written quarterly exams. Suppose if half early exams has been written that okay again that role number will be
repeated. No role number will be repeated because of role number will be repeated because of that it's a half exams that it is what that it's a half exams that it is what here now half exams that we go for
here now half exams that we go for there. 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 on that column name references parent table of name
name references parent table of name column name has to go let's see now that guys this is the example we'll make it that here now one point we'll make it like this what is the first I'm taking parent table as a
customer serial number unique that means here it is a that okay leave it mother 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
Yes. So then here first I'm creating a parent So then here first I'm creating a parent table. So that create a table customer. So that you write that is serial number it is into unique.
Okay. Int unique I'm taking that. Then C name I'm making that is worker of some 20. then location I'm making that so where care of 10 so make it a default value okay what
that is hyd so then I'm just taking what that is so account number what that 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 the sense no duplicates no nulls
that is the difference very good question that everyone thinks that primary key in this sense only unique not that one primary 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 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
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 ninth just see there let me finish this
ninth just see there let me finish this okay this is one thing and now one more student uh role number I don't know for example if I'm giving like this here again here it won't allow it won't allow
already one null value you used one more null value in the sense again unique constraint violated again unique constraint violated here so that is one point you have to remember it won't allow here no
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
also will not be allowed completely unique and not null. I hope you got it unique and not null. I hope you got it that understood.
Yeah ma now I am creating parent table here. Yes, I created 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 names. So trans only making some shorten names. So that serial number in uh I'm giving here now auto increment. It is a primary key for this table. Auto increment in the
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 okay then t type
will go that I'm giving okay then t type you just write on t type is work of sum you just write on t type is work of sum 10 then t date transaction date it's a date column you just make that easier now then amount what is the amount has
now then amount what is the amount has been s one second that h here account number you write on it's a vare of 10 I'm write I have written that then here what is another one amount just to
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 wanted to make primary sorry foreign key for this column we wanted to make what that is foreign key here. Now here you
write on yes yes 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.
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. 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.
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 there here you can see that is so if
it is having mul that means it is having foreign key constraint that is the meaning you have to remember mul 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 there guys.
getting right now. Okay guys that is one thing. Now let's go for data adding. So we wanted to go for insertion of data. Insertion of data always should be done first parent table then child table.
Insertion of data has to be done. First parent table then what? Child table. Okay. Add some rows into the parent table. table. Add some rows into parent table. 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 so 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 just three rows are inserted. See there just 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 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. Now here you just check the now I'm checking the foreign key. So here I'm just going for account number four.
Keep it like all the transactions are same. same. Just go for this. Let's see this is see you got an error here. See there you got an error over there.
Here now it is not inserted. What is that error statement? that error statement? See there here
error code 1452 cannot add or update a child row a foreign key constraint fails foreign key whatma that account number references to the this is so you are
trying to add account number four but account number four is not present on the by 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 credittor and here now
some 14th date okay the something yes accepted 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 th000 rupees just have like this some transactions some random transactions I'm adding that now you go for here see there now
got it up the something like we are having this is guys I hope thath it is understood understood or not
guys please let me know if the primary key foreign key creation relationship tables What is your query here? Uh if I want to 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 I'm 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 reference say it should be refer reference say it should be refer customer table it's a parent table now
which column it should refer account number column number 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
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. this one. So this is the one we go for a deletion.
Suppose right now for updation is okay. Leave it that. So for a deletion what we have to do it in the sense see that guys first we have to delete rows from child table then parent 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 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 delete from customer the simma here where account customer the simma 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
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
number equal to 0 to see error code 14 what is so 1451 cannot delete or update what 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 that 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 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 first you delete rows from the transactions
deleted rows has been deleted two rows has been deleted. You check that here. one is there 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 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 the parent table then delete that particular uh account details but it is
particular uh account details but it is a little bit cumbersome task or not guys tell me little bit cumbersome task or not it Obviously
something but when you go to the live databases you have n number of rows are 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 us one option. What is that? In the sensema if you apply a delete command on
sensema if you apply a delete command on parent table. If you apply a delete 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
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 the parent table. So there is no meaning of that by deleting a row from the parent table. So delete row from the parent table and as well as delete that
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. Ondee cascade. When we apply this option to a child table. When we apply this option on a child table, it allows to delete rows from
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 in 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 dbd D okay department
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 uh 30 so here like department like the three departments I added now
let's have that if yeah fine now I'm creating here create
table emp okay here now emp number it is into okay here now emp number it is into primary key then e name where care of some 20 I'm not adding all other columns my leave it that Okay, department number
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
Now see that I'm applying foreign key okay foreign key of what we write department number references what I'm taking that DB of department number after writing this here you write
number after writing this here you write on on delete cascade space on update you have to write like this then close it
it you have to Write like this here now. you have to Write like this here now. Okay. Then go for executing that. Now insert the employee data here. Insert employee data. See there. Insert
Insert employee data. See there. Insert into EMP info empa 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 my 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 DP 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 DPD where department number is
from DPD 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 here. First check on the DBD table. Yes. Department 20 is not there. Now you check data in EMP table employee info table.
Okay. See there now department 20 employees are deleted automatically but EMP info EMP 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 practice data sets on the assisted proxics data sets lesson on the assisted proxics data sets lesson number five is there. Sorry.
number five is there. Sorry. Not that. here. 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
site guys and please confirm that you are having or not these three Excel are having or not these three Excel sheets.
week only. SQL data sets you downloaded last week 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 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 yes there you have got these three excel files
if anyone is not getting I'll share it on into the chat box if required it up. Yeah, got it up. Now these three tables now we have to uh like what do you say
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 guys, 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 there. This is different categories of
Yes, this is also one of the parent table. table. Now here we have one table called sales. We have one table called sales. is there in the sales table
guys. Here now see there now ordered date guys you see there that this is the sales report it is a child table of both 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 the foreign key column that then one more is there customer name is there but actually we don't require this customer name column they have given for a
reference but we don't require Why? Because in the sense even if we keep Because in the sense even if we keep nothing will happen. Okay. But customer 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. Product code, product name. I think it is. So yeah, if it is same, no worries. Got it.
Then product code is there. It is one of the foreign key from the product table. Product name. This column also we don't required. required. So totally we have a 26 products. So 11
customers are there. I think 11 customers. But here how many in a sales customers. But here how many in a sales table how many sales we got it here that see there now here totally how many rows are there my here now. Okay here row so
11 rows are there 11 in the sense 10 products has been sold out of 26 products. Yeah, fine 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.
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 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 ma 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 the this time products. time products. Select the products table and next.
And here that products data set you remove that products. remove that products. Make it products and go to the sales. 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 verma sales
guys here guys a small modification do it here now in this sit situation okay in this situation uncheck this C name column P name column uncheck those
two columns. Uncheck that C name and T name column and go to the next and next name column and go to the next and next and make it finish. Yeah guys, now my
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 tableer 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 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
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 Yeah, how we can go for retrieving data from multiple tables
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 which column on which table which column you want to retrieve that you need to go. So table one dot column 1 table 1 dot column 2 table 1 dot column 3 like
this again another table called table two dot column 1 table 2 dot column 2 like this you have to mention then afterwards from table one join type
table two table one join type table two and on join condition this is the process you have to take join Join condition in the sense what he gave here what is a join condition here
let's see join condition is a condition join condition is a condition has to be placed common column just see common column of joining tables.
So that is one thing just we are taking here. Yeah. Common column that is common column of joining tables. What how the condition should go on table one dot common column equal to table two dot common column.
example customer dot customer id equal to sales doc customer id or else products.p product code equals to sales.p Product code it comes like this.
While writing queries you will come to know while writing queries you will come to know here. Then what is a join type? Here we have a joint types are there. Here now what is this joint type in the sensema here there are different type of
sensema here there are different type of joints as follows. One is my here inner join is there. Second one outer joint that again classified into two types that left outer joint right outer joint. Then we have a cross joiner. Then we
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
Let's go for here. What that is inner join. So what is the inner join here? 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.
So that's what we do here. Now let's see that how it comes right now let's see that how it comes right now there inner join how it comes there inner join how it comes one second guys so yeah
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 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 you can write. First we write one join carry for two tables. Okay. So now so anyhow you are seeing here. Now see there
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
customers inner joy coin sales then on what is customers do C ID is then on what is customers do C ID is equal to sales dot C ID
equal to sales dot C ID you should get like this here customer's table when you forget that
select star from here What is the star represents here? All columns. Now I'm bringing all columns from customers table. 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? 13 customers are there. Okay. Now this is what we are having. Yeah. Now this is what we are having. Yeah. Fine. And uh you go for the sales table.
You go to whatma that is sales table. You go 10 rows are there. Then uh that You go 10 rows are there. Then uh that means 10 products has been sold by uh here row 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 there 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 these all this is for what customers What is this? Customers here. Now go to the sales. How the inner join
you. Take this column. Take this column. Okay. And I'm copying that. And here you just paste it. That is Yeah. And this is for what? Here now
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 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 four ones is there yes we have here so that particular details will come then 121 you check 1 to 1 is there is or
then 121 you check 1 to 1 is there is or not? Yes, it is also there. Yes, it will not? Yes, it is also there. Yes, it will come. Then 1 12 6. Is there any 1 2 1 6? come. Then 1 12 6. Is there any 1 2 1 6? I think so. No. Then 1 2 4 6. Yes. 1 2 4
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 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 3452 is there. Is there any here. Then 3452 is there. Is there any 3452? No. Then 3 921 is there. Yeah. 3 3452? No. Then 3 921 is there. Yeah. 3 921 it is there. Here. See there. Here
you have. Then 5334 is there. Then 5334 is there. 5334 is there. Yes, it is also there. 5334 is there. Yes, it is also there. Yes, that is. Then 9021. Is there any
Yes, that is. Then 9021. Is there any 9021? I think so. No. 9212. Yes, it is there. First one. So then 9875 is there. Yes. Here we are having 9875 is there.
missing, right? Huh? Yeah, that means 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
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 these 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 uh Now executed this what ma execute this process. Yes. See only we have 10 rows.
Yes. See only we have 10 rows. 10 rows we are having that is just wait
customer ID, customer name, location, phone number. Then see there ordered date, order number, see there customer ID and what about that is product code which has been purchased this addresses.
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 show see there show all customers who
are purchased what is who are purchased products which products purchased we don't require here who are purchased the products who are purchased the products
products who are purchased the products that that's it I want that information that that's it I want that information so this query I'm just taking into like so this query I'm just taking into like this what is that here
this what is that here okay customers dot you just go for customer name that means key name that is one thing that we are having that is one thing that we are having okay so customers dot then what that is
that's enough I want only those two columns that from the sales table what I 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 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 the 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 explaining 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 that if
So then how can we go for that if [snorts] so for this here the products is there right now okay so now what I'm taking meth that
select products dop name okay then now products dot price 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 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 here going for products inner join then sales
then what method is on products dot p code is equal to sales products dot p code is equal to sales dot pore code
that we need to go this is one Again
name is unknown. What happened
Products is spelling wrong. No, no. Spelling wrong. Products. See that? Here. Now we got a 10 here. That is so corn is the price is 50 20
stock is there 10 has been sold. See 50 into 10 500 p is 10 rupees and it quantity two items has been sold 20 rupees total stock is like this. See
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 now I want 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'll go over that. But one more point I want to discuss here. A simple point in this one here. Every time we used to write table name customers dot customers dotproucts
dots sales dot something like that. It is somewhat cumbersome one when the table names are very lengthy. So shorter names is okay. So that these queries you can write on with table aliases.
with table aliases. For a table you can have aliyah's name. Okay. Now for this one you can have table alias names here. Now using table alias table alias should go. What is that
table alias in the sense you have to go for like this. So table alias in the sense for every table you take one or two alphabets as an alias name. So for products I'm taking P. P is an alias name for
taking P. P is an alias name for products. Okay. P dot P name then P dot products. Okay. P dot P name then P dot price then P dot what that is stock. For 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 in the sense my here products as P
here products as P inner join sales as yes. So it is 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
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 and mandatory for joins mandatory in the sense even you can
write on with the table names also 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
aliyah's names all queries you can add with alias names the queries whatever we have chosen all these queries you can add with aliyah's names only add with aliyah's names only this is make it C dot star
make it S dot star so customers as C inner join of sales as yes then here S do C ID C do C ID
then here S do C ID C do C ID see there with alias name 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 aliases right now here
been taken column aliases right now here we are writing table aliases so take one or two alphabets as a alias name for a table that you have to use name for a table that you have to use Little dog.
doing guys. Is it clear with alias names?
Yeah. Now, so here join with three tables. Three or more than three tables. But of course, we have a three tables is there. I'm writing three tables part. I'm writing three tables part. Guys
listen here three tables. So first of all you have to make it observe that from the three tables 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 my here from the products table. columns my here from the products table. You take these all
P name has been done P price P stock P stock quantity yes quantity yes amount stock quantity yes quantity yes amount has been taken that and suppose if you want that ordered date and All
what that is 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 Okay. So now you see there guys from Okay. So now you see there guys from [clears throat] 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 join sales as yes
then on those two columns common column 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 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 that my
inner join products as P on see that my here P do P_COD is equal to S.Pore is equal to S.Pore code. You have to write like this.
First between customer and sales then sales and products or else 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. Now three tables join it is see there the customer stiffen is the customer has been purchased corner to it's a 50 rupees okay like 10 products he has been
purchased amount is 500 he has been ordered on this date to this is the order number so complete details you have
so products you just go from 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 this is order number five this is order number three you have like this here
guys is it clear guys writing the query join query for three tables
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 yes exactly with the products table in yes exactly that's it only attaching that is yes
that's what only here you'll get the clarity on this kumar sorry karan uh next few minutes just wait okay in the next few minutes that because we have a join is there cross join there I'll explain there you'll
come to know the one but your understanding is fine that correct okay that's what I'm telling you first of all while writing join query first of all you have to make sure that which is parent table which is child table
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 Disma here. remember that if you're writing a join queries so on this join queries what
about other clauses we discussed earlier in select statement where clauses order in select statement where clauses order by clause group by clause having clause these all can we implement? Yes, of course we can implement
the requirement should go for here. Now here we are just checking on only order sorry join checking on only join for example let's checking on only join for example let's see that is I do one thing
see that is I do one thing go to this product okay suppose if anyone ask that so that is here now show
here now show all the customer and products [snorts] all the customer and products [snorts] 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 you write enter join query see there now take this join query here and after this you add where clause
where it's a perfume from the products one p dot category P dot category is equals to what should we write me that perfume
wear clause Yes,
Hey spelling mistake is there 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.
there. No. Yes. Now you write on there. That's correct. Ma'am, no perfumes has been sold. sold. Snacks. Now you'll get it.
See there. 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 wave 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 rows from joining tables per matching rows from joining tables per matching and non-matching.
and non-matching. What that is matching and non-matching non matching rows non-matching values of joining tables
non-matching values of 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 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 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
if value present 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 is what we are have this is.
So what that is left side table that so let me write one example for this. let me write one example for this. So now here guys so let's see that is a So now here guys so let's see that is a left order join
take the previous examples only so leave about three tables first go for so leave about three tables first go for with the two tables uh first one what take this is the take down to the products
left order join what is the question here now show sold Sold and unsold products info. Suppose
products info. Suppose sold and unsold products info.
So now here so sold unsold in the sense sold so sold unsold in the sense sold products available on sales table you know that unsold the the products information level products table so that
I'm writing like this here okay and here I'm writing in this way what is that uh left outer join
uh left outer join sales something like this we are having. sales something like this we are having. Okay. Now let's see that just let's see let me take this one for an explanation prospection
prospection in here this quiry you 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. Now 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 See here product codes are there. You bring these all these product codes. 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
go for this line here uh for the word left outer join what is the left side left outer join what is the left side table what is the right side table what is the left side table what is the right side table for the left outer join
right side table for the left outer join word on that statement yeah what mother is products is the left side
table sales is the right sided 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 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
not guys everyone Done.
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 left side table to right sided table in join
table to right sided table in join statement. Here if values are present in see if values are present in left side table but not present in the right side 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 is 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. Eight. No. 9. No. 10. No. 11 seven. No. Eight. No. 9. No. 10. No. 11 is there. It will come. 17 is there. is there. It will come. 17 is there. Okay. This is then 16 also there.
Okay. Then 20. What mother is? 20 also will be having 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 then here 1 to 26 all values just see then here 1 to 26 all values are compared with okay now sales table present okay not present null values will come null values will come here
that is so that is one thing just you have to go see that output mark left have to go see that output mark left outer join no yes see there we have a 27 rows Product number one is not there. So, so this is
number one is not there. So, so this is the sales part no quantity no amount in the sales part those details you cannot find only product table we'll find the details so that is coming there see totally wherever it is not having
see totally wherever it is not having values in that will come into the null that will come into the null here matching non-matching both the values are coming here guys getting or not my Okay
guys, is this quiry is understood or not?
Guys please let me know matching non-matching. here? If [snorts] it is understood then
current the conditions has to keep it that but just when I'm writing only core line only no okay I'm writing only core
uh case statement there where 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 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 uh comparison goes from sales to product sales to product in the sense where is that arrow symbol
where is that arrow symbol not this is oh sorry yeah so here. What is that? We get it right now. First 11. Yeah. 11 or 11 is there. 17. 17. Some 17
Yeah. 11 or 11 is there. 17. 17. Some 17 will go. Two. Two will go. Then 20. 20 also will go for that is one more 20 will go. 16 will go. And 19 also will go. That is then five will go for here. Then 25 last but one will go. Three will
go. So all values are completed from right 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. 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 left side 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
this is I'm taking here and here I'm just going for that uh here now tell me the result now if I write like this
if I write like this what is the result
This query result is asome as right after 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
in a join statement. [snorts] I hope it's clear with the left and right outer joints right right outer joints right clear or not guys.
Yeah, let's go for another uh join cross join is there. What is this cross join? You know uh
What is this cross join? You know uh so this type of this join is a join process join process. Without a join condition.
join process. Without a join condition. Without join condition. Here let's write on a query without join
condition. Now what will happen if you write that?
So take this is only take this process only here remove this join condition and here you write on cross join.
here guys listen [snorts] that uh what do you say that is um yeah how many rows are there in a sales table a products table guys? 26 rows we have right?
How many rows are there in a products table? 26 rows. How many how many rows are there in a sales table? 10 rows. Okay. Now see the result of this is which one that yeah see the result of
which one that yeah see the result of this one here.
So what happened here? What happened here? Ma you guess that what happened? What is the result of this one? What happened?
Yeah. Multiplication has been done. 26 rows into 10 rows multiplied both 26 rows into 10 rows multiplied both tables. 26 into 10 260 rows. So see tables. 26 into 10 260 rows. So see there one product has been mapped with
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 curtsine product. Carter sign 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 from that one to retrieve actual rows we are we have to keep a join condition.
We are you 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 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'll get see there you got the 10 rows you'll get see there you got the 10 rows only here
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 is 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 curtain 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 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 self join we go for what here now self join here
so what is a self join here what is Okay. Self join my here. uh implemented on single table on single table when
column to column relationship 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 we will not get that is but self
join goes on single table only one table on one table and one table how you write 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 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.
we won't mention here. We won't mention here that. But uh the thing is right now whatever the values are there in employee id column same values are exist in our manager id column to whom is working under whom that is no
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 this? What is the occurrence will come here in the sense? Now see I want to know that who is working under whom. I want employee name
or their manager name. I want like that. What what I want in the sense of manager What what I want in the sense of manager name and employee name. I want yeah select start from employee is that is what see the relation here. Now
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 Jet Hale. I want like that. I want like this Janet H manager is
okay. Like E002. Who is 002 here? that uh okay like Cynthia Brooks I want like uh 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 you are 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 whatever that is inner join or outer join that is one thing. So how you take one because we have a table alias is there no table aliasis is there that based on
the table aliasis you can do that what is that you see that how I'm writing the query here. So select see there e dot first name 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 so here from emp as EM emper join okay emp as m
join okay emp as m on e do emp on e do emp 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 Holland. Even if you want you can check that I I Even if you want you can check that I I removed.
See Ryan is an employee. 583 is what? That is here 583. Jonet Holl is a manager. So Janet Holly employee manager is what? Cynthia Brooks. Okay. Cynthia Brook manager is what?
Arthur Block. Arthur Block himself is a that is the same thing you can check 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 it's a lost 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 actually here I haven't get any messages or any responses from savvi 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 now the core concept of joins. 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 two inner join and outer join that outer join also left outer join and right outer join that's it.
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. joins is there. I hope it's clear with everyone.
This is the today's topic guys here now. 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 what do you say that uh views 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 Stored procedures is another concept. We are having like this. No, not this one.
Not stored procedures that is also will be there. Before that uh user creation user creation uh when go to the user creation grant comma revoke
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
things first we'll keep it one thing these all we keep it top side and that this we'll go for top side then this is the order we will make it the order we will make it this is the order we go for that
yes is my very first one like we are having here so in our command programming in our command so like we have create we have worked on create alter drop, insert, update, delete, select
only these commands we worked and we have two more commands are there language command that is commit and roll back. Then second one data control 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. Okay. So that is the very first thing here like what is a
commit and roll back? What is a commit and roll back method? So whenever we go for commit and roll back the commit and roll back are essential transaction language commands. just see there 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 [snorts] while working on it just come down to here okay this is okay find that
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
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 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 need to control here. Okay, control in the sense for example you added some rows but within that rows some of the rows or all the rows 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 uh removing of 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 rows I don't want it that so that I wanted to cancel that uh transaction last for transaction I wanted to cancel just listen my point that that is one case
or else suppose we did some modifications in a rose we did some modifications in a rose that but that modifications I don't want that. I wanted to the previous state and now I I wanted to cancel that
transaction or else I have I have deleted some of the rows but some important rows has been removed accidentally some important rows has been removed. So how to get back those deleted rows?
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 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 add 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 we
So these two things are there that database will give that opportunity to database will give that opportunity to us the database or 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 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 you go for undoing that it is not possible. Okay. Okay, then again you have to go for other commands like delete or something that this is what but for this so
if you want to use these command that means make permanent changes or make undoing that the databases part basically see till now have done yesterday also we have done a session we created lot of different
tables that but uh anyway we haven't used that commit roll back commands why because in the sense now error. Basically, SQL will go for auto commit. That means once you add a new row, permanently add it 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 used. So before starting your transaction s so here we
starting your transaction s so here we have to go for set uh auto uh commit is not there here. So once again guys that command uh
here we will make it that set auto commit.
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 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 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. or else you just go for better zero. Okay that is one thing.
Yes ma'am this command. Now we can work on the transactions. Now we can do okay now we can do transactions that so that is so now see there now you go
to this table guys now you go to this table now I'm trying to insert some rows guys you see there that insert into emp info so that values
I'm giving that something like 106 six. 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 a
yes something like I added two rows you see there now so now the rows are added 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
select star from emp info in the sense here you will get it that Right? See that it is showing old data and new data both are showing here. Okay. That means it has been saved in the database table but not permanently.
table but not permanently. It maintains. So what we can call cachy memory in the cachy from the cashy 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 m simple that is and make it this is see roll that is and make it this is see roll back executed. Now you check whether those two rows are there or not in the database. See that 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. 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 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 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
is equal to one of the student name like see I did like this here. So Rama Rama see I did like this here. So Rama Rama is there no ramani Ramanika. Okay the 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 given Ramanic but we have stopped the permanent transaction so that right now here now we have an opportunity what is that made roll back.
Okay. So now you see there the old data will come that see that that old data has came. So like this we can check but uh after update if you go for commit and then go
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
Okay you delete all rows. No worries. I see I deleted all rows from the table. I deleted all rows from the table. See no rows are there. But since we have switched off that auto commit now with the roll back, we will get it 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. you know if auto commit is equal to one or auto commit is on by default it is on
that's why in these many days so you know we are we haven't wor that commit know we are we haven't wor 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 so Now if you want to work on this roll of all you have to switch off that auto
commit so that set auto commit equal to zero you have to made it then only you zero you have to made it then only you can get
set as one in MySQL works and data is saved permanently by default can we perform no we don't it is not possible to perform that roll back okay you have to be switch off that If we commit, if we committed deleted
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 10,7. You go for that sig is equal to 10,7. You go for that sig details. I'm deleting right now. Yes, I deleted. Okay, that is one thing. Now you go with
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 that whether it comes or not, you will come to know. Yes, I did a roll [snorts] back. After that, uh just go to here. See the soft that, uh just go to here. See the soft has came. Yes, once again you delete.
has came. Yes, once again you delete. Yes, I deleted. Okay, you see in the database table not there. Now made commit. Now made what? That is commit. You made it. Now the row has been deleted
permanently. Okay. Now try to get the roll back. After the commit I'm doing roll back. After the commit I'm doing roll back After the commit I'm doing roll back that I did a roll back also. Now execute
that I 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 command roll back won't work. Do roll back box works only undo the lost 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 again lot of things in the same this commit and roll back just listen this commit and roll back just listen this commit and roll back uh I been used till now on only one statement. Ive used one insert then I shown the difference
between commit and roll back then I used only one update and I have shown commit and roll back differences and I used delete and commit and roll back. Okay. delete and commit and roll back. Okay. Now see there here
Now see there here yeah just come down to here for example this is the emp table. So 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 here uh some modifications. Okay. Some modify. Then after that uh I did some delete or
else I did this insert and modify for example that means couple of rows has example that means couple of rows has been inserted and I have seen some data the rows where it has gone some mistakes.
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
afterwards if you use roll back it won't work it won't work that is so remember that one point so before commit whether you do row uh 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
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
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 modifications 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 that if you want to delete or something you have to go for a delete under commands that is we have to go for in
that way. So that you should be cautious that while working on these two commands 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
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 is here we
have to make this one we have to switch off auto commit equal to zero when you switch off set auto commit equal to one then you have to go for that permanently
doing that is but these things permanently in workbench you can do some permanent settings on this even that update commands and all that is so where update commands and all that is so where in the sense of just 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 some rows You added some rows that suddenly your system has restarted all rows will go off. All rows will go off of that. That means
all the transactions what you did before commit to all those transactions will cancelled. &gt;&gt; And with that we have come to the end of this SQL full course. We hope this course helped you understand SQL in
depth from database fundamentals and basic queries to joins data analysis and more advanced database concepts. If you have any doubts or questions, drop them in the comment section below and our team of experts will be happy to help
you. Don't forget to like this video and subscribe to SimplyLearn for more career focused tutorials and full courses. Thank you for watching and keep Thank you for watching and keep learning.
