---
title: 'SQL Full Course 2026 [FREE] | SQL Tutorial For Beginners | Advanced SQL Course | Simplilearn'
source: 'https://youtube.com/watch?v=5SFj4V-F4xU'
video_id: '5SFj4V-F4xU'
date: 2026-08-08
duration_sec: 27502
---

# SQL Full Course 2026 [FREE] | SQL Tutorial For Beginners | Advanced SQL Course | Simplilearn

> Source: [SQL Full Course 2026 (FREE) | SQL Tutorial For Beginners | Advanced SQL Course | Simplilearn](https://youtube.com/watch?v=5SFj4V-F4xU)

## Summary

This video is a comprehensive SQL tutorial for beginners, covering everything from basic database concepts to advanced topics like joins and subqueries. The instructor explains the importance of SQL in data management and provides hands-on demonstrations using MySQL Workbench.

### Key Points

- **Introduction to Data and SQL** [00:07] — The video begins by highlighting the importance of data in everyday operations and introduces SQL as the essential skill for managing and retrieving data. It mentions that database administrators remain crucial for organizations.
- **Course Overview and Promotion** [01:01] — The instructor promotes a professional certificate program in data analytics, mentioning collaboration with IIT Delhi. The program includes 180+ hours of learning, 40+ exercises, and 15+ industry projects.
- **Agenda and Core Concepts** [02:10] — The agenda is set: introduction to databases, MySQL concepts, and installation. The instructor emphasizes the fundamental cycle of data, process, and information, using the analogy of constructing a house.
- **Programming Languages vs. Database Systems** [09:43] — The video distinguishes between programming languages used to develop software and database systems used to store data securely. It highlights that every software works with a database system, which is often invisible to users.
- **Why Tables?** [15:13] — Data is stored in tables for easy accessibility and structured understanding. The instructor explains that tables allow for quick comprehension of data through rows and columns, unlike unstructured sentences.
- **Limitations of File Processing Systems** [19:17] — The video explains why databases are preferred over file processing systems like Excel. Key limitations include limited data storage, lack of security, difficult data access, and data duplication.
- **What is a Database?** [26:02] — A database is defined as a collection of interrelated tables of an entity. The instructor uses a school database as an example, showing tables for students, employees, and library information.
- **Introduction to DBMS** [39:52] — DBMS (Database Management System) is introduced as software that manages databases. The four main operations are Create, Read, Update, and Delete (CRUD). The instructor also mentions the 14 Codd rules for DBMS.
- **Types of DBMS** [47:04] — The video explains the evolution of DBMS into RDBMS (Relational), MDBMS (Modern), and ORDBMS (Object-Relational). It emphasizes that RDBMS is the most commonly used, introducing the concept of primary and foreign keys.
- **Introduction to MySQL** [01:01:47] — MySQL is introduced as an RDBMS tool. The instructor explains that SQL is a universal language for databases, with 90% of syntax being the same across different tools like Oracle and SQL Server.
- **SQL Command Categories** [01:06:12] — The video outlines the five categories of SQL commands: DDL (Create, Alter, Drop), DML (Insert, Update, Delete), DRL (Select), TCL (Commit, Rollback), and DCL (Grant, Revoke).
- **Creating a Database in MySQL** [01:08:13] — A step-by-step guide on creating a database using the 'CREATE DATABASE' command. The instructor demonstrates how to execute the command and refresh the schema to see the new database.
- **Using the 'USE' Command** [01:16:30] — The 'USE' command is explained as a way to switch between databases. The instructor demonstrates how to select a database to work with, highlighting the active database.
- **Creating Tables and Data Types** [01:19:51] — The video explains how to create tables using the 'CREATE TABLE' command. It covers various data types including INT, FLOAT, CHAR, VARCHAR, DATE, TIME, DATETIME, and BLOB.
- **CHAR vs. VARCHAR** [01:25:43] — The key difference between CHAR and VARCHAR is explained. CHAR is a fixed-length string that always occupies the maximum space, while VARCHAR is a variable-length string that only uses space for the actual data.
- **Inserting Data with INSERT** [01:35:34] — The 'INSERT' command is demonstrated for adding rows to a table. The instructor shows how to insert a single row and multiple rows, emphasizing the use of single quotes for strings and dates.
- **Understanding Constraints** [01:49:42] — Constraints are introduced as rules applied to table columns. The video covers NOT NULL, UNIQUE, PRIMARY KEY, CHECK, DEFAULT, and FOREIGN KEY constraints, explaining their purpose and usage.
- **The UPDATE Command** [02:11:05] — The 'UPDATE' command is used to modify row values. The instructor demonstrates updating a single column, performing calculations, and using the 'WHERE' clause to update specific rows based on conditions.
- **The DELETE Command** [02:36:36] — The 'DELETE' command is explained for removing rows from a table. The instructor shows how to delete specific rows using a WHERE clause and warns that deleting all rows without a condition empties the table.
- **The ALTER Command** [02:40:03] — The 'ALTER' command is used to modify table structures. The video demonstrates adding columns, deleting columns, modifying data types, adding/dropping primary keys, and renaming columns.
- **The DROP and TRUNCATE Commands** [02:56:03] — The 'DROP' command deletes an entire table or database, while 'TRUNCATE' deletes all rows from a table. The key difference is that DROP is permanent and cannot be rolled back, whereas TRUNCATE can be rolled back.
- **The SELECT Command and Clauses** [03:05:41] — The 'SELECT' command is introduced for retrieving data. The instructor covers the SELECT clause, including selecting all columns, specific columns, performing calculations, and using aliases.
- **The DISTINCT Clause** [03:24:24] — The 'DISTINCT' clause is used to show only unique values from a column, eliminating duplicates. The instructor demonstrates its use on department and country columns.
- **The WHERE Clause** [03:30:00] — The 'WHERE' clause is used to filter rows based on conditions. The video covers various operators including '=', '>', '<', 'BETWEEN', 'IN', and 'LIKE' with examples.
- **Group Functions and GROUP BY** [04:08:52] — Group functions like MAX, MIN, SUM, AVG, and COUNT are introduced. The 'GROUP BY' clause is explained as a way to divide a column into subgroups and apply these functions to each group.
- **The HAVING Clause** [04:24:08] — The 'HAVING' clause is used to specify conditions on group functions. It is always used with 'GROUP BY' and cannot be used without it.
- **The ORDER BY Clause** [04:33:27] — The 'ORDER BY' clause is used to sort rows in ascending or descending order based on one or more columns. It is the last clause in a SELECT statement.
- **Subqueries** [04:42:49] — A subquery is a query within another query. The instructor explains how to use subqueries in the WHERE clause to avoid hardcoding values, such as finding employees with the maximum salary.
- **Primary and Foreign Keys** [05:12:47] — The video explains how to create relationships between tables using primary and foreign keys. It demonstrates creating parent and child tables and enforcing referential integrity.
- **ON DELETE CASCADE** [05:49:44] — The 'ON DELETE CASCADE' option is introduced to automatically delete rows from child tables when a parent row is deleted, simplifying the deletion process.
- **Introduction to Joins** [06:04:39] — Joins are used to retrieve data from multiple tables that are in a relationship. The instructor explains the syntax and the importance of common columns and join conditions.
- **INNER JOIN** [06:09:12] — The 'INNER JOIN' retrieves only rows with matching values in the common column of both tables. The instructor demonstrates this with examples using customers and sales tables.
- **OUTER JOINs** [06:40:08] — The video explains 'LEFT OUTER JOIN' and 'RIGHT OUTER JOIN', which retrieve all rows from one table and matching rows from the other, filling non-matching rows with NULL values.
- **CROSS JOIN** [06:56:55] — A 'CROSS JOIN' is a join without a condition, resulting in a Cartesian product of the two tables. It is not useful for production but helps understand the importance of join conditions.
- **SELF JOIN** [07:03:05] — A 'SELF JOIN' is used to join a table with itself when there is a column-to-column relationship within the same table, such as an employee table with a manager ID.
- **COMMIT and ROLLBACK** [07:15:00] — The 'COMMIT' command permanently saves transactions, while 'ROLLBACK' undoes them. The instructor explains how to disable auto-commit to use these commands effectively.

### Conclusion

The video provides a thorough, hands-on introduction to SQL, covering fundamental concepts, essential commands, and advanced topics like joins and subqueries. It is an excellent resource for beginners looking to build a strong foundation in database management.

## Transcript

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