sequel or structured query language is a standard
database language that is used to create maintain
and retrieve relational databases Started in the 19
seventies sequel has become a very important tool
in a data scientist toolbox since it is
critical in accessing updating inserting manipulating and modifying
data Understanding this we have coming for this
sequel tutorial Now before we go ahead with
this session I like to inform you guys
that we have launched a completely free platform
called Great Learning Academy I have access to
free courses such as a iCloud and they
still marketing We can check out the details
in the description below Now let's have a
quick glance of the agenda We will start
off with an introduction to DBM Essence equal
Then we will learn How are equity date
our it's equal After locked we will work
with joints and sequel and finally we will
work with Windows Functions and sequel So we
gonna dio this week The scores it's called
Database Management system also call as D b
M s heavy hard about what it what
It is the BMS So anybody Any idea
what is D B A missile What is
database It is someplace where you stole data
So just a quick review For my knowledge
How many are here from I t background
I get Got some information Okay Okay So
even if programming No no on I d
known I should not be a problem Databases
Not more into programming Thes are simple commands
One line commands where we're trying toe uh
extract data out off the data So there
is a place It's called a database where
we store all the data And then um
we extract data from it by how we
will learn about that But the basic concept
of BBM essence there is some data stored
somewhere And as a daytime list we have
to deal with data in order to process
that data In order to analyze that that
we have to extract that data from the
database then do some time for processing on
it So we learned all about that on
DA It's also called a tock waiting and
manipulation using SQL So what What is our
aim being a data analyst All the data
test to query they out off it and
we want to manipulated manipulate means we want
to process it Let's say Fi gotta They
are information about a batsmen stored in the
database We want to get that information out
of it Probably trying to find out It's
the batsmen Average score OK um his age
are probably how many matches he has played
your things like that So that is called
manipulation and quitting Guest How Toe extract data
out off it Okay so why would he
be a mist or why we are here
today uh doing this sports And there was
another term called There s Q L So
we'll talk about more for this Um SQL
stands for structured query language and will use
this as a to like You have used
Piper to do some data processing before This
is another type of language that is specifically
for the basis Okay databases A special term
What happened Um 2030 years back or going
back to eighties and nineties We were dealing
with only data Which waas structure data Do
you know the difference between structured and unstructured
Just a simple definition Structured means which we
can for didn't rules and columns You like
you work in excel You have call on
You have rose nicely looking data in proper
format that is called stuffed your data And
in the start in the beginning when this
I think you know was the blooming most
data that were idyllic Waas structured The banks
were using these the airline reserve the really
reservation system and the big companies like Wal
Mart They were storing their transaction Onley in
the structure format Okay so why And now
there is unstructured data Also I What is
instructive data Anybody An example Your WhatsApp messages
Your emails Your excel She's your word documents
the images that you send you click a
picture the videos the argues that you send
You know all these things which you can
not put in a in a table like
nice looking by rows and columns that is
called unstructured So we have hardness both structure
as were less unstuck to data They say
that these days 80 to 90% of the
data that we generate is unstructured data Okay
but it doesn't mean that structure data has
lost its You know John it's not like
that many companies still extensively work or structure
did think about your banks Can the instructor
didn't know even what many here and there
You know they are at a loss Okay
so it's still many um fortunate 500 companies
They deal extensively with structure by We will
teach you by turn and many more other
tools to deal with unstructured data But next
four days we can only work with structured
it Okay Well book with a nice looking
rows and columns because many companies still have
bad as their asset The most valuable asset
This structure data with me Okay so SQL
is That's the name SQL structure Grady Language
So databases structure data and we really angry
SQL helps us first store They kind to
the database and then extract data out off
the database Why Because we want to analyze
it We want to get hidden pattern out
off it I want to get information out
off it which we can use it to
um toe grease our business value to have
an edge Jonah Competence Okay we deal with
sales Right Colette Severe balm RV deal with
sales So I want to find out what
does my customer base And then there is
a date best We're all one million customers
information in the store I want to extract
that information I went toe analyze it What
is the average age off my customers in
this region Okay From which locality Most of
the sales are happening Which more Wal Mart
is doing better than the other locality Which
products are going fast in this region of
that region Is there any sees infected Okay
so I want to win lies All that
information That's why we need to extract this
data analyze it and tell our business that
Okay this is what this story is where
the data is telling Okay the BlackBerry roll
off A data scientist is to rant on
raw data into actionable insights Very very important
Okay As a data scientist as a data
analyst we want to extract that raw information
The one column which has customer ridge and
turning do inside inside invaluable insight The every
day is so if we target audience this
age group but this product you know we
can sell food we can do up selling
We can do cross selling things like that
Okay Much off the world's raw data From
customer transactions to medical records lives in organized
collections off papers court relational databases We'll talk
about more of what I relational databases Right
now Let's remember there's a data base where
we get dust Doctor Data and we need
to get data off how to store data
in that database That is not our primary
focus Because when we goto the industry the
data has already been created Somebody has stored
in there are job Most of the time
is to select it out off for a
focus Mainly will be not to store data
nor to modify data but to extracted So
that will be our mean folks Clear sofa
kids Okay so you become ineffective That data
scientists you must know how to extract how
to analyze process data using a language course
secret And the scores teaches you that SQL
is a core and foundation skill Without its
proficiency you can hardly become a data scientist
because ever you go to any project and
industry they will dealing with and most of
the time 90% off the diet time The
data is not stored in some excel or
files anything like it stored in some robust
database Okay We're the proper security constraints are
their backup is done Everything So it Zaveri
Valuable Estate Porter Company We will deal with
all of that You don't worry about 10
Okay So what was happening before Like long
time back 60 seventies when the i t
things come into picture the traditional approach waas
to store by store everything in fights like
we do in our laptops Right We have
some information Quickly Open award dog click Open
Excel file Restoring that It's okay for us
because we're only one person storing this much
amount off data Think about Wal Mart Think
about the Wall Street back The wealth management
backs You know how much data think about
Citibank How much they that they have And
can you really store it in file one
file or pen files No What is the
problem I think for let's say this example
it's a bank again So they have So
they have our system They have a process
for loan They have a process for flexible
You don't go to bang we can get
alone can do on FB there And then
we are throwing money depositing doing all these
transactions So let's say there are three Three
processes is one of the three There will
be many many mood And let's say there
is a very north using our database They're
just using a file system some Excel files
or some award file they're using So how
will that look like Probably they're storing files
like this One is the customer details Let's
say new customer comes into the bank and
they need store Name a pan card Does
that all the information Somebody Probably He has
not enrolled into alone or fixed deposit Just
a new customer Okay so there must be
a file Their call cost Monday vehicles And
then there is a file with customer loan
customer FD and customer transaction The problem with
file system is that the files cannot talk
to each other tactically without any any other
program Return on it Okay so they have
to store the person's name address phone number
Here also here also hair also and okay
think about redundancy Think about this More storage
space that they need Think about the person's
address has changed So what they have to
do they have to change it into four
different places We have only listed for there
can be 10 Okay so what The problem
is file system is the ISA piles not
talk to each other We have to store
the whole data into one file Similar date
Another file another five another Okay Another problem
You know the file how the files there
are you know lines in the file And
let's say that you have a customer Let's
say uh we want to search a customer
in our file by phone number So it's
the situation search you know What is the
situation Said go line by like this They're
spying Fine fine fine And how Through the
processes Okay effort And when the file has
let's say 10 million customers how slow it
will become how difficult it will be for
us to search something Okay then there is
also you know file system doesn't know where
is the phone number So probably Fyssas and
we have written that on the 30th position
in a line the phone number starts So
how difficult it will be for the programmer
or for someone to go to the 30th
column and then search for that phone number
then extract the whole life Okay so those
are probably those were the problems with file
system So we wanted to get order native
to store such a large amount off data
So these were the problems in the traditional
file approach Also security also right If we
have we don't want to show customers address
and phone number pan card at work right
A car to everybody Advance We give them
the file access there's no control Okay So
that that was the other problem Multiple users
file can be used Only one person you
must be get together in person on the
same file you get that is locked by
somebody else You usually get that message So
again Not a very user friendly way off
dealing with data Okay so that's why we
need a tilting And we came a bit
this concept gold Okay what is a database
It's a collection off information organized for easy
access management and maintenance Look quickly search for
this phone number Give me the customer information
quickly Manage If the person you know is
also getting loan FB we will use We
need not to enter the customer information again
We can just link that customer toe a
new FT or to a new loan and
men tennis again The person's address changes easily
Change in one place everywhere it is reflected
Okay so the examples there are many examples
EU saw data We are surrounded by databases
telephone directory customer data product and went register
weather records Everything is Indiana bees You do
do an air ticket booking You do Ah
railway booking You go to a hospital There's
a hospital management system where they're storing it
into a database So all structured data It's
the database No this the Donkor database And
there there is a term called database management
systems What is the difference So what is
a database and what is a database management
system We are here We are users Okay
Then there is this database which is a
storage space You can think about it like
a story Space will be a storing our
data on rows and columns Now they were
did what did they The The developers are
the people Pardon It's difficult to go directly
goto the database and extract data out off
it The same five system problem Probably We
need to remember where it is located its
physical position things like that So they came
up with something called DB a mess in
between where we talked to the be a
mess and DBM s stocks toe big nappies
And what is a DBM mess It's a
software It's a software full of programs Okay
And that is a lier between us and
database We go to a hospital right We
There's a nurse there he she doesn't know
about What is software What is all that
thing He just opens one window and that's
a nice looking user friendly window She enters
her phone number she searches are record and
then we're going for this Test the stairs
She quickly does that She doesn't know what
is going on behind the scenes She doesn't
know where the data is stored How to
fetch it The word is a physical position
Off it hardest toward is the phone number
first door or address after that or vice
versa She doesn't care Okay because there is
a database management system leer in between That
takes care off everything Okay How the date
I stood in which order In which file
At what position So database management system is
a It's a blockage It's a heavy software
Okay that that helps us to goto the
database without worry about how to fetch data
So a database management system is a large
software package Consist off a collection off interrelated
files and set off programs files again to
store data actually physically But I still stored
in files But how Distort how to excess
it baby A miss takes care of that
We has a user name No do bury
about Okay And the primary goal off BB
a mess is to provide a convenient and
efficient way to store retrieve and mortify information
Clear what is a database and what is
it The database management system SQL is the
only way Yes as far as their If
we buy a database management system let's say
articles Okay Article comes with SQL And how
you can talkto data through sq it No
there is no other way because they have
made it like that You cannot go into
the file You know I access one file
some and go there at this bite changed
They don't know that that you know defeats
the whole purpose off us different you know
getting up separated from the physical characteristic off
the data Okay They say that you worry
about what data is telling you instead of
worrying about how distort how you will existed
how that you will change it They have
given us a very very easy method of
doing that using SQL Okay so that we
need not to worry about the physical location
and the nitty gritty Softer Okay I would
request everybody all I should be here Nothing
will laptop when there's a need Open laptop
I let you know Okay we'll be sharing
all the resources so just try to grasp
the concept These things will know the laptop
thing We'll we'll do it live We'll have
enough time to do that Okay Because these
are some of the very very basic concept
of DB a mess And from the interview
point of view that is okay But from
actually understanding what is your mess It's very
important to listen what I'm thing and try
to grasp as much as you can Okay
so that was the BMS what it helps
us to do And new files Insert data
Retrieved data modify data removed data and removing
fights will do all of these things Whatever
the problem was with file systems Okay those
are taken care by the BB amiss So
what were they sharing off base across application
You know Citibank They have branches all over
the world but there is only probably one
database article They must be using article because
only that can handle or that amount of
data at one place and everybody probably millions
of users are using their de tubby's every
day Okay sharing off data Cross application reduced
data Redundancy right Customer Did your story only
once Enhanced security An ism So they baby
miss gives us a way that if we
have a table and we talk about you
can see excel She is a table rules
and Collins papers Okay so we stored a
time table form in inner database so we
can provide security mechanism in such a way
that let's say if there is an employee
table and I only want 10 persons in
my company to see the salary column off
that employed people I can do that I
can also do it do it that my
employees table has employees from all the departments
be the count's finance marketing And I can
make a security in such a way that
the account manager will be able to see
only account employees Finance can only see finance
employees and things like that So you can
even go to a set security at a
cell You know what I sell a combination
off ruin column been set security even excel
level So that is the benefit off Working
with PBMs data independence What we call it
we call a data independence You know if
we directly go from here to here were
dependent on the physical characteristic coughter data But
because there's a d b M s in
between We are data independent Means how the
data is stored How physically we would access
it in which ordered it is stored Okay
um has a phone number stored First address
after that Advice Worse up has the accounts
employ store first and then the finance Our
employees I don't worry about that I'm dependent
on how the data is stored in the
I only need to know two things What
are those two things Those two things That
the name off my table and name off
the column which I won't retreat Okay For
example I should know if I want to
get my customer information I'm only interested in
what is their name And if in the
email address because I'm sending them some awful
Okay so I need to worry Okay The
first is that customer name then the address
in the phone And then they will it
No I only need to I want I
need to know customer name off the table
which is customer and the name off the
columns I won't name and image These are
the only things that I need to know
Otherwise I am independent off how the data
is stored Ordered You know all those things
Data independence given important Um it's also called
data abstraction So anybody talk about these Jagan's
data independence they extraction It's a simple meaning
We're independent off Harder Data is too Okay
clear Better flexibility Okay Again you know um
let's say we were starting the last name
as 20 characters before Right now the you
know we're getting or long names We want
to make that column bit as Latino from
20 Don't be Let us to 30 letters
in the file system It would have been
a disaster to do that because you know
the every line come every exposition we have
accorded with every position But here a lot
of flexibility You can add new columns You
can add new rules in between rows or
doesn't matter which order you store You can
increase the column bit You can add more
columns You can add more tables So all
these things without disturbing the application that you
know you have created over it like ever
is given example of hospital management system the
nurse is using getting probably They're increasing the
name off the patient you know the length
of the patient behind the scene and the
application will work as such No changes that
dot OK so that is the better flexibility
in fourth integrity constraint It's an important integrity
Constraint looks very fancy but did it's not
that difficult in a critical strains mean we
are validating something We're big putting a constraint
on it in such a way that it
iss consistent Okay what can example of this
Let's have a column cord it huh So
what is the first constraint or first to
make it a good data What is the
first constrained you think about it It can't
be It can't be negative It can't be
more than 100 up 110 120 Something like
that The Celery column can be negative hard
earned It can't be No I can't be
blank space in it if it's unemployed All
these constraints which makes our data consistent good
excluded You can put all those constraints in
the database So by creating a table I'll
say that Let's say if it's a bank
and it's their policy they'll hire people they
have Can have a customer in the age
group off 18 to let's say 90 something
like that whatever their policies any constraint which
that business has decided Okay you can put
all those constraints and the database will never
never be allow you to enter anything beyond
those integrity Constrict Okay so that's why when
we create database it's not a one day
exercise It's expands months We think about what
should be the type off the column type
of the column means is it an indigenous
and decimal It's a string What should be
its lent What should be the check that
we need to impose on it Okay so
things all of these things we can do
with this better transaction support will talk more
about bad and four standards and backup plan
recovery features As a data analyst we need
not to worry about backup and recovery features
That's the job off the database administrator called
DB But you should know that it is
taken care off Okay so we talked about
database We talked about Libya Miss Everybody clear
about what is DBM is what is data
this And now we're talking about database mortal
What exactly it is It is that when
we're storing the then be a starting big
guy here how we can store their tiny
did a couple of metrics We can store
it So how we can store database that
this call database more than and there are
three ways of storing it We will talk
about two very briefly and then we will
talk about the card one in debt A
database more does specify how our databases structure
and used It's a conceptual tool So it's
a concept that how we're storing the data
how it is organized and things like that
It's a blueprint for a database for software
application so these are but three diets off
BITTA Morgan and B They record beast record
basics We're entering the room in a room
So if it's a customer there 10 customers
for in our bank were storing 10 roost
there 10 records It's a record by recording
and the first one is Hierarchical Network and
then relational And there's something called Object based
Also I'll talk about that Also let me
start with hierarchical Hierarchical is nothing It's it
We were using the problems 60 seventies Vendor
database thinks you know start it and how
be stored it It's like an hierarchy Any
organization chart There is a VC here and
then you know the other managers there and
then the developers there Similarly the college database
undergraduate course postgraduate course and things like that
So when you store data in this form
it is called hierarchical Morton It was okay
before then The data's size was very small
The problem with this is that in order
to get let's say I ask you Please
give me all the students Data are the
course data Undersea is computer science The limitation
in that this was that you have always
need to start searching from the root and
then reverse your way here which was taking
if it's a small later Still it was
okay But as the data size started increasing
this becomes very cumbersome thing So no no
not usable right now I don't think there's
any company who is using hierarchical just for
your concept that it was before stored like
this Then we also tried network database mortal
and where you know you have it's like
a network Okay But you have seen the
network off cables one thing going from another
So it wasemiller toe back And we have
links from this file this file and sort
things like that And again the problem with
this was the same If it's a small
data set it's OK But when the data
size increases and there are so many arrows
going from here here here here it was
very difficult to query data also So again
we got rid off that are it's not
used that when I still miss concept is
still used You know when you learn about
machine learning the further courses There is one
unguarded Concord new RL network Okay they used
this concept for that All off this artificial
intelligence What They're trying to go there tryingto
simulate our new electoral which is in our
brain They're trying to simulate it so they're
also they use some type of net modeling
so you learn about briefly in further revenue
will doing deep learning or machine learning But
for the database it did not work So
what we did we moved toe relational data
models It started in eighties And what is
today It's 2019 40 years or 30 years
after its invention We are still using it
So it's a very powerful and very very
Um you can say enhanced version off how
to store We're still using it And you
go to any bank They're still using a
database Forget about instructor data that is there
But there real estate is the structure data
which they have stored in a relational database
Not hierarchical not network relational database And how
do we store data in Relational Mordor It's
the exact same way Restored it in accent
Okay what have we have here is a
look at this Pay attention So we have
our relation Name Student What does this student
So any object you know Let let's say
take great learning So what will be in
their database then In their wake up database
there will be a relation or a table
called students There will be a relation or
relational Samos table relation Cordelia instructors probably for
courses And then there's a combination off students
and courses and a combination off instructor and
courses Okay So all are there objects which
for which they want Oh do some a
nemesis Okay so we store our data in
a form off relation And if you look
at it this is a student relations and
they these are called the These are the
columns in the relation And we have a
fancy name for it called attributes And these
are the rose And again we have fancy
names court apples We call them Rose We
call them records We call them topples Okay
The columns We call them columns We call
them attributes also Okay So any relational database
we always store data like this This is
only one relation We will probably have another
relation which is course this and probably have
another relation There be which is a joint
off students and courses because all students will
not take all courses right and all courses
will not be taken by the students That's
why we need a card relation also which
is a combination off another two relations So
we talk about people into that how we
will do it But the concept should be
clear What is Relational data Okay so data
is viewed as existing in two dimensional papers
known as relation It consists of unique attributes
and shoppers to sometimes there can be you
Have you noticed him There Annuls also here
Office for number Knowles What does it mean
in a relational database then they do not
have any information We do not put the
blank that we do not put the space
there We don't put a zero They're automatically
the database puts our value cold No There
No None Doesn't mean blank Not mean zero
It means bad data is not available And
there is a special very off handling notes
Okay So whenever you see it now it
means that that data is not available in
the database Any database was logical Organization is
based on relational data Mordant That is called
a relational database So they bms is most
off the time Also called as are the
B M ISS Why are because right now
100% off the baby and mrs that relational
database management system not there are people not
network Last point So what is what I
have told you that this is my relational
data Mortal relational data murder So any database
that uses this type off relational data Mordor
is called relational digna bees So that's is
just simple I think you know what is
a relational database that uses relational data model
And what is it doing A model It's
a logical view off how the data is
stored in the database Clear baby A metal
were you know talking about a lot of
things Their database the bms no rd be
amiss Okay there's minor difference but it should
be clear in your mind Okay So another
example I don't know whether it's visible or
not Ah so we have customer loan records
from customer loan table and this is my
customer table So customer has customer i d
customer last name with a lame first name
account number account I bank branch and customer
email And now I want So can you
see it in the file system If we
have ah Lorne paper these columns would have
been repeated there also But here what We
have done what we do here We only
have this master table you can say which
is having the customer detail And now that
customer has taken alone So we will not
repeat these columns again We will only repeat
this customer I d in the other relation
and then put alone number against it and
then amount in dollars And that will be
a link between this relation toe best relations
No need to repeat things So now that
customers address has changed we only need to
change it How many places Huh Only in
one place And which is Edda Customer details
people Redundancy is removed Inconsistencies removed Storage space
is take less taken Okay All those benefits
And a few more times again I told
you these are called attributes columns fears and
these are called rules record stuffers Different books
of different website used the's names but they
are all the same things Number off attributes
and how many columns are in the relation
that is called the degree off the relation
that just another come you Can you remember
What is the degree of the relations How
many columns are there And what is the
card in ality off papers It's simple means
that offensive or now Cardinal It'd mean how
many of those are there You know they
d B is They wanted to be looking
but FNC not talking in a normal language
So degree card banality couple attribute simple things
But right now just remember that customer idea
is the link between these two tables That's
the common key between these two papers on
which we will try We'll find out what
is the customer information along with their loan
information in one go Okay I manager asking
me a report off all the people who
have taken a loan from us I want
their name address phone number along with how
much amount they have taken as alone So
we will later Nash I'll teach you how
to join these papers to get the whole
information in wonder Carnality is how many rules
are there in the people So what is
the cardinal It Let's say off this this
table for what is the card banality of
this stable fight Okay just a fancy name
We will use it But it's good to
know You know if somebody is talking in
these terms you should be able to understand
what they are talking about But here we
will talk about what is the normalization and
all of these things It is the degree
off the second table And what is the
degree degrees The number of columns in the
table So 1234567 and 88 columns are there
So degree off this table Is it right
now Cardinal ity only mean I teach you
What is that One is to end and
all of that later Right now just remember
card in ality is how many rules are
there in the paper We'll talk about it
later How the two tables are connected is
that I want to one relationship One too
many will talk about that later But right
now just simple Remember that these are the
fancy terms that we use when we talk
about relational data more Okay so let me
go back So we talked about these three
things Record based logical Mordor We talked about
hierarchical network Relational These a record base where
we entered a te record by record Now
there is something called object based Logical mortal
So what is this Let's try to understand
What is this thing Okay So what happened
then We create tables or relations in an
organization Um it's it's in the database Okay
If somebody warms to if you join a
company day and you want to know what
Arte what is their database look like Okay
what is the database you have joined the
bank How What is their database look like
What are the different tables in it What
are the different columns in it How they
related to each other So to make that
thing easy for people to understand we came
up with the concept off object pieced logical
borders and what it is It is nothing
but a pictorial view pictorial view off our
neighbors and our columns Okay Lets you know
uh you have up Let's say you have
data about with our Kohli How many matches
he has played in different continents Something like
that You have data points Nobody will be
able to understand it Then you have a
pie chart telling us in this continent he
has paid this much of these many scores
He has um you know scored in this
continent this continent So by check is what
It's a pictorial view or what the data
is trying to tell you And we use
it by so that it's easy for us
to understand the same way that we want
to understand the it a base off any
organization We create a pictorial view off it
And that pictorial view is called object based
because what is that day But we consider
this as an object What are the different
objects in my organization how they related to
each other So that's a pictorial view off
our database and it's called entity relationship model
And how does it look like it looks
like this Okay So you today you joined
an organization and somebody gives you this Nobody
will have time to make you understand You
know what are the neighbors What They will
just give you this Try Go figure so
you can get a lot off information Also
this So what other things This is It's
the name says H I database So what
can you tell me from this picture How
many tables or relations are that Seven And
you can also figure out that there is
an employee stable And what are the things
An employee table It's employee I d First
name last name E mail all those things
And then you can figure it out that
employees is somehow related to our department It
also has a job and then it also
has a job history and then job is
related to some location and location is connected
to some country And there is also some
think oil region there So without anybody telling
you this you can figure out a lot
of things about your organization Okay you can
also figure it out The job ideas here
in the employee papers So what does it
mean That you can also figure it out
that the company policies like that one employee
can have only one job i d At
the time Um employees can only have one
department at the time Only one man did
Manager These are some of the things which
are not you know return somewhere But with
this picture you can figure it out on
your own And this picture is called He
are diagram or entity Relationship diagram And what
is entity here Entities Nothing here Entity is
equal to table Our entity is also equal
toe relations because of the relational database management
system And then what of these lines These
lines are the relationships That's why the name
entity relationship More than okay So every organization
has the DB must be having this entity
relationship model It's also very very important when
you start building the database Okay so when
you start building the databases the organization has
just stopped it Somebody sits and that they
draw these things by hand or using some
tools then they a lot of brainstorming Okay
so this table we need here this relationship
we need this constraint we need So this
is a massive excess sighs but stocks with
the yard diagram and er diagram there special
tools to create your diagram I usually you
know in my organism were using a tool
called urban e r The blue I am
but I don't need No Two very But
there are tools which can create these type
of pictures for you and then using these
pictures Those tools can create scripts Scripts is
what it's accord which will you used to
actually create these Tabor's into the database Okay
this is not only the picture The tool
will help you to create a picture and
then you say create core for me and
they will create accord The court will look
like this Create table employees with columns This
this distance the language The court will be
in sequel language and we will run it
and sequel and the same picture will be
created not the picture Actually tables will be
created in the database So whenever you go
to a new organized whenever you go to
a new organization you have no idea about
their functionality You can just simply ask somebody
Can you give me the year diagram off
your database I just want to study it
and by only looking at it because nobody
will sit with you And you don't tell
you all about how the business runs You
could get a lot of information about their
data about what entities they deal with Okay
using the your dagger Okay what is in
er diagram And this is We call it
an object based logical model gate A fancy
name but just a picture off your database
so that everybody's on the same page and
it will never be like this Okay in
my last organization I the year diagram it
was printed on I 200 pages you know
And it has I don't know 1 50
tables and so many relationships so it's not
easy But even if you get that complicate
to start from one end Mr I'm looking
at this one corner and then said OK
how this related and move your Trevor's through
the whole thing You will not be able
to get the full eat everything but Bill
Gittel an idea Clear everyone again We learned
database I'm again recapping database BB a mess
already be a mess and nada Yeah Dyker
Okay So what are the bad things What
do you think What are the red things
in here The pink things huh They are
that is that uniquely identify a paper It's
very important for us Tour we should not
create a table without a unique key by
because you don't want anybody by mistake to
enter same employees twice then garbage in garbage
out Okay that is another constraint that we're
putting to make our data Very good Great
Consistent That nobody should be able to enter
an employee choice So that's why whenever we
define papers we always always define their primary
key Yeah it's all let's consider all all
the same lines There is no matter do
it I don't know why they created these
different colors but for now these are simple
relationships Relate one table related to another table
And we're gonna talk more about this Mordor
because we use this step this database to
learn SQL Okay so we will spend a
lot of time understanding all off these things
Okay so this is another example We have
a student relation and uh these are the
attributes And these are the choppers And another
Tom don't mean a set of atomic values
often attributes sometimes what is the domain off
a column The main off a column is
what we lose we can and turning it
Okay For example let's say If it's um
let's say it's American status American status is
a column and we can enter only poo
values in a single and partners Okay so
single and partner will be called the domain
off that column Just another name for you
to remember Okay so he attributes So every
table has as a tool you key attributes
and non key attributes Okay And what are
those The key attributes that participate in the
selection off candidate key Okay so let's talk
about some of these things So all the
keys keys is column The keys which can
uniquely identify the table are called key attributes
So in this example of stewed A how
many key attributes are there We can identify
the student uniquely by student i D By
email idea because name can be repeated and
hopefully million emails are not repeated and probably
the mobile number So these three key will
be called ducky attributes and everything Escort the
non key attributes clear simple enough ski and
non key and then we will talk I'll
talk about some of them how we identify
the keys there spend special names and you
shouldn't be able to understand those because the
database people talk in these stones Okay so
please understand these terms One key is called
a candidate key And what is the candidate
Key and Nikki that can uniquely identify a
table is called Candy Dickie in the last
example What were the candidate keys Can you
tell me there are three candidates King student
I d email I d and mobile number
So first we identify the candidate keys among
that candidate keys we identify know which one
really define as a prime Ricky and primary
is the one bitch We tell a table
that Okay this is my primary key You
cannot repeat this key You cannot put null
in this key So usually we go bid
indigenous by because there is another You know
you go by searching It's ah searching on
Indigenous are faster than the next So we
always try to medic indigent at a primary
key Okay sometimes if there is no primary
key in some scenarios we create our own
primary key and we damn it 123456 Thomas
usually usually avoided what the business says From
the business point of view you should identify
that primary key Okay so everybody should know
the difference between a candidate key and a
primary kids Self confusing sometimes What is Candidate
key Any key which can uniquely identify a
table is called the candidate key And among
candidate keys we pick one key and tell
the SQL that make this s primary key
and that will uniquely identify the table So
these were the three primary three candidate case
and we pick the student I d S
candidate Keys is a concept Please remember it
Candidate cases a concept for us But I'm
Ricky is something we tell the sequel Kordic
tell the that this is the primary care
of the paper Okay We will never use
the case level Board candidate key system concept
but primary Give me say we write the
word pr I am a our bike primary
Okay In the court when be at creating
a table it cannot have no value And
you cannot repeat value in the primary key
And then there is something called composite primary
key What does it mean Composite What What
is the English meaning or do you understand
from Kompas it more than one Sometimes in
a table We cannot have only one column
as a primary key If I go back
to that uh you diagram Can somebody tell
me what is the Where is a composite
here Huh What is the compass IQ Primary
key here huh In the job history by
Because what is job history does It is
storing unemployed can start as a developer Then
he will move to senior developer then analyst
then take manager then technical manager so there
can be many job ideas he has been
associated with So we want to capture that
information and we capturing it in job history
Employee I d And then we also have
a job I d But way have made
the scar did as the primary key not
the job I d Can anybody tell me
why is that unique Is employees idea and
job I did that job I can be
given to somebody else also because the unique
KIIS employees idea and job I divided We
make that as a key No it has
not already been used This is a table
I could have made employee I d and
job idea as the primary key No job
I do think that is the purpose will
be Want to capture how the job I
d is changing It was developer first and
senior developer that analysts senior analyst Because for
one employee the job I can repeat how
I started as a developer Okay then I
become senior developer And then I became analyst
and then they remove I I didn't want
to do the technical thing I want I
asked them Make me a manager So they
made me a manager I didn't like my
job as a manager I want to go
back to the technical thing So they changed
my jaw and I want to become an
analyst again I don't like the management stuff
And then again Bina analyst So I was
analyst before also I'm analyst now also but
if I made employees idea and job ideas
a primary key I could not have captured
that information So what I did instead off
making that I made start dead as a
primary as a part of primary key by
because big will never come again Okay I
wasn't an ally and a listen to 2015
I'm an analyst in 2019 Also Okay so
that's That's a lot off your thought process
goes behind I don't define what is the
primary key and we have more than one
call up in a primary key It is
called compose IQ Primary So another example Students
mark stable So we have two columns here
Student I D and subject I d And
you know you It's your name and then
a fight on your name D B A
mess So So it is not necessary We
only have one column as a prime begin
Many tables We have the composite because they
are the combination of things like in this
example Similarly in student course stable there will
be a composite primary key student I D
and courtside Then there comes a foreign key
Now let's see how what is a Ford
And again this is up and department i
D And then this department is related to
another table which is called the department paper
And it has department I D as a
primary key And here the primary key is
employee I D And this is related to
this So what is a Foden key Let's
say I had five departments in my organization
their ideas and 2030 40 and 50 This
is account This is financed This is sales
Things like that a new employee is added
has joined us Okay a new employee has
joined us and I have to create a
record for that employees So I give them
another employ i d Let's say this I
entered his name whatever it is and then
I have to enter his department idea Let's
it ideally and should have picked did from
these five departments But I put 100 it
should I be allowed to do this No
Okay so what I do I will put
up constraint on it that you are only
allowed to enter daytime toe this column which
is already there in the department people Okay
It's a validation check Foreign key is for
validation purpose It's again and integrity taken stream
I don't want anybody to enter any department
for my employees It should come from whatever
departments we have identified and put it in
our department table Okay Otherwise people will start
and bring weird things and the data will
become garbage Okay so this is a constraint
That we put So how I will put
that constraint I will make this department I
d I will define It is as a
foreign key foreign key always always connected toe
a primary key off another table Okay so
this foreign key I will connect to my
past mint Okay And when I define it
as a foreign key you know when I
define it I put f o r e
i g and while creating the table that
this is my foreign key and I also
need to give This is for two which
primary key this is related I will define
This is a pardon key and it is
related to this primary key or this people
And what I'm telling the system is that
whenever you enter daytime this department i d
It should not be anything but whatever is
coming here Okay so that is the concept
off foreign key And it is again a
very fancy name The name off this concept
is re friend Sheer integrity again logically also
makes sense We are putting a reference to
some pain and we want to have a
integrity constraint Integrity means everything is I Could
it Okay So any review question on when
people talk about did you put the referential
integrity check for this column What they're saying
mean that they want to put a check
that you should be able to allowed enter
values only from some other table That is
that That segment So this Oh God here
in this table will be called a foreign
key And every foreign key will definitely be
a primary king in some other table Otherwise
they will given better Yes Between the composite
primary key and and the poor donkey OK
it's a totally different things So let's say
student I d and subject idee and then
mocks This is my table Okay concept wise
What I'm trying to save in the stable
The ad with students have score helmet How
money marks In which subject Okay so forget
about everything else If I You have want
to store this information what do you think
What should be our prime Ricky Okay so
it is so feed And then the bms
and then 70 marks So mead by kind
75 months So people were saying that student
I d is the primary key hot build
this book No No OK OK so student
idea make it like this pen And okay
there is another paper call student and it
has student I d and name and things
like that So this is another table My
primary key is student idea So that is
a separate master paper where I have all
the students It is possible that I have
a student who has not given any exam
yet Okay So his data will only be
in this stable now But now he has
given an exam and he has scored something
I want to store that information somewhere I
cannot store in the stable because the stable
is meant only to have student information as
one line I cannot enter that stream student
again in that so I cannot store the
marks here So I have created a new
table where I am having three columns Student
ideas subject I D and Marks and I
want to know that information So if I
create this this is a separatist Now tell
me student I can it be a primary
king This No And again it is an
Adana day But core some check 90 right
there is another course A table called subject
and subject idee is let's say their primary
key So here I'm storing all the subject
There are hundreds subject you have not The
student has only taken five subjects Right So
this is a master table off subjects No
Now I want to find out which scores
that person has scored So I have created
this new paper And now tell me what
will be the primary key off the stable
Then there can be another's today with BB
a mess and that can be another student
with bike subject I can be You know
there are many students for DBM who is
starting the BMS ministering We're starting fighting Yes
So in this scenario we will consider that
what are thes column as one primary key
and it will be called up composing primary
key It's just the concept We never say
somewhere Right composite It's just the concept So
in this scenario people use the composite primary
key Foreign key has not entered in our
picture yet Okay Now we will air the
foreign key thing in the situation So what
We're will The foreign key comes now I
wonder there are student ID's in this table
Let's say I go there a clean toe
the stable and try to enter a student
I D 100 which does not exist in
the student table which will be a good
idea It will It will make my data
garbage So what I do know I don't
want anybody to do that What I will
do in the stable only I will define
this key as my foreign key and the
foreign key is related You have to give
a table to which it is referencing So
this foreign key will reference this stable and
I don't want anybody to enter any subject
It should come from that master subject table
Whatever the university has defined that these are
the subjects which are available Okay so I
will create another foreign key that will be
the primary key concept is different It's for
different purpose and foreign key concept is different
and it's four different So see the primary
is composite key but it's Each key is
referencing some other people One table can have
leave one primary key but one people can
have multiple foreign keys foreign key May or
may not be unique and normally extort unique
Think about this We're repeating the student many
times We're repeating the subject many times So
in the main paper it will be unique
because that is the primary kid has to
be unique But the foreign key may or
may not be unique for in this example
a student table It has a column called
Branch I D And then Grant I D
is a foreign key to another table which
is a branch Sometimes we also use the
terminology as this is the parent paper and
this is the Chinese Okay the bond The
table which is having a primary key is
called the parent paper and this is called
the Child People And sometimes you also say
that there is one to many relationship between
these two tables The one value off this
can be repeated many times in go for
it Clear for is a very very important
concept So it should be very clear to
all of you foreign key needed for validation
purpose so that we don't enter inaccurate information
in our data That's why it's also called
re friendship integrity fencing him But it's just
the validation Like we put a check on
age it should be between 80 to 90
18 to 90 Similarly we're putting a check
on student I d That it should only
have student ideas Prom It's master table where
the students date Eyes first stood Yes exactly
Very good point It's defining a domain You
can enter values only from this domain in
this column So all these is interconnected I
can put s i d hill and can
still reference to student ID because I'm telling
the table this is afford and cato this
stable whatever the name it will take Whatever
the name we have given it need not
do match with the with the primary key
column Okay So what was the plan today
So you people want a break or there
was no break plank huh You would have
been Yes ma'am You didn't ask any questions
But this time Yes man it's OK I
also thought that it's to ours 45 minutes
is going to be a little tough I
turn Probably do some off the programming but
here it's more of one liners So probably
today we gonna do a little bit more
on SQL And again I'm warning you that
gave as we do more and more things
The things become a little confusing sometimes but
again have patience Uh do this through a
lot of practice Go through the recordings again
and again Probably a couple of times Go
through the PPT like your own notes Write
your own court in the sequel And I
think you will be absolutely fine So yesterday
we covered Ah the theoretical part off the
BMS and then some off the heretical part
off SQL We told you there are different
command groups anybody Okay So should I give
you five minutes to talk Then we can
start Yes You have paid for this class
I will be more than happy to give
you five minutes Okay So you have all
the time you know to talk among yourselves
You wanna be in this scores for a
long time so just you know pay attention
here because that's what you have paid for
And that's what why you were here So
catch up on your gossip later Or even
if you're discussing something related ask you hold
onto that first Let's go through what I'm
teaching and then we will talk about Al
will discuss with each other with me things
like that So which command groups sauce SQL
has many commands We have only done select
yesterday I'm gonna dough only select today also
But again we have ah number of commands
and we have categorized those commands Anybody has
any clue What are those categories The first
one is Dick Um we did select in
it Second one is the D l which
is data definition language will be changed the
structure off the table Then we have the
m l which is the data manipulation language
will be changed The data OK we'll be
changed The data says Then we have B
C L which is the data control language
Grand and Riverwalk And the last one is
D C L Which is the transaction control
language Commit and roll back and we're gonna
talk about more about that We are very
much interested on this thing Beak You win
Ah select statements So we did 23 clauses
in the select statement yesterday So we have
select what other thing with Mito Definitely havin
a select statement wrong And we did one
more after that No in is an operator
in is like equal toe When bu in
wear clothes be defined employee I d equal
toe this not equal toe This less than
greater than in So that thing will come
here in the class What is an alias
Giving another name to the to another A
To a column Can we use aliases in
the wear Clothes can be used Leases in
the order by clause Yes So these are
some of the things that you should be
clear about Okay so we were working with
HR scheme and the whole world waas ah
to three things I think that the blue
is CP thing Everybody has done that Yes
because that will be counted and evaluated You
there They're taking care off tax so please
make sure you finish back And then there
was one take home exercise and uniting submitted
Tow them You find it out how to
submit and they're gonna evaluate that also And
the third thing I gave you the my
questions lest your classroom activity that is for
your practice Any query or question in these
three things that you want me to discuss
which was not clear you were not able
to do it Just let me know and
I will start with that first Okay so
can care Does nothing It's like an English
interpretation We're goingto can catch some things in
my while displaying my column So I will
say Celek I will say Can Cat I
can put any number off arguments or para
meters in context It can be a column
name It can be any string OK so
I start with employee I d and then
comma and then I'll put space works in
closing This is a string comma works in
department I d from employees So what This
will give me Okay so it will give
me employee I d Let's say 1 2001
employee I d works in the intent Something
like that So can care does nothing It's
a function you can pass it many para
meters pedometers has to be distinguished by comma
You can put column name and you can
foot string or you can put anything you
can put I don't know but it makes
sense You can put 100 hear anything here
and that will return as such It's just
concur Tin ating columns with strength and returning
back And why do we do it sometimes
toe Um right The nice report or a
nice heading which is Everybody is able to
understand We do that Is it still the
Koncak Okay question number four will start with
the right to query to select 1st 10
records from a table Yes I did not
cover this Cheever So let me cover this
one also So whenever you have I'll give
this example also I only want the 1st
10 rules off this Kuwaiti Any equity Okay
I will use the keyboard course limit and
then say Then I can give any number
here and what it will do It will
run this query without even looking at it
first Okay So if there is an order
by here or the by employee I d
So it will run this query first without
even looking at the limited in So after
this Kuwaiti do whatever it wants to do
order by this that sorted do everything After
this it will just return me the 1st
10 records Okay so we're gonna put limit
in there and then it will return I'm
going toe talk more about limit because I
think there's another question you will have today
off limits Let's say I am ordering by
salaries and salary descending So when they ride
salary descending what will happen What will be
my first regard High Yes Salary Let's say
I want to find out first top five
employees who plays for getting the highest salary
I'll write a limit Five After this let's
say I want to fight Find it Employees
were getting said the second highest in the
card highest second highest and the third highest
How will I do that Okay If I
say telemetry it will give me the top
three Right I don't want the top when
I want 2nd and 3rd highest selling How
will I do it There is a concept
called Offset in limits So what will happen
Limit what How What I will write I
will write Limit one What is one One
is my off sec offset Means uh uh
You have to start after the offset after
the offset And then how many records I
want I want your regards Okay So the
Ben we put two things and limit its
meaning changes It sees a calm mind between
There are two things now Its meaning has
changed Now their skill will understand it like
that My first number is the offset off
That means after this start counting after this
start getting data And after that Harmony records
I want I want reports So this was
a game in the second and the third
highest Selfie Let's say somehow I want to
find out the 5th and 6th highest salary
So 5th and 6th highest celery I will
write Know what was for So I will
put offset as four And then I would
Yes So if you put any number after
limit the normal functionality is that it will
run the query first and it will whatever
the result come talk five thing Whatever the
top five records it will return That's pretty
simple We're limiting it to five records But
if we have run a query and we
want reserved from in between in between that
is not the top five the middle five
or something like that Okay we can do
that also How in this case we have
toe give two para meters to limit Not
only Bun okay When it sees two para
meters it inter Brexit like that that the
first pyramid er is the offset and offset
means start after this record start after this
record and then how many records you want
Comotto I want to records So if I
put a limit then fight So then is
my offset And how many records I want
I want five records So which records I
will get starting from 11 to 15 And
what is 11 Whatever the query returns after
ordering buyer whatever you have given in the
query after we got the desert from there
the 11th to fill 15 3 chord I
will get okay Is this clear Let's quickly
run it also so that we'll see that
is very close So whenever you open the
open your skull vote bench If you can
see right now I pl there is some
other database that is highlighted It means right
now that I peel isn't used So if
I write here select star from employees you
think it will book No it will Lord
book because employees table is not in the
ideal schema it isn't in which schema hr
schema So you can have many ski mother
Many databases you need to be in that
database Toe access It's stable So what I
do I will I can make the h
er the default schema by two ways One
is double click on it and see it
is highlighting So now it doesn't use the
other thing You can do it You can
right click on it and make it set
as default schema So as soon as it
high life it's highlighted Only then you will
be able to run the quickly Yeah so
if you quit is not running you're getting
another message table Nazon exist able See it's
written here Table i p l dot employees
doesn't exist because employees table is not there
in I peel schema So that's why change
the schema and then run it So let's
say Ordo by sala de descending okay and
it's gonna give me the employees with salary
as the maximum 24,000 month for month support
You I don't know Okay Check Okay So
25 4000 is the highest Now I want
the second and the third highest So what
I'll do I will put limit If I
put limit five it will just give me
the the first five record See there there
are only the five records Five rows return
And if I want the 2nd and 3rd
I'll put the offset one It means start
after one and then give me the three
records and it will give me the 2nd
3rd and 4th highest said it will be
17,000 17,000 and 14 What are the other
things you can do But I don't think
you can OK but so how do you
do Oh okay How do you do Get
help So you have to The official help
of SQL is help Help index and then
you can type limit in it And it
will give you all the arguments that you
can faucet And then you can check whether
you can do that thing or not I
don't think you can do it Limits on
the table column count and rose size So
you just check this help Okay Okay So
question for done Question number 10 Somebody asked
in this vile Only after it also list
all I related Okay list All idea related
departments were There are no managers Let me
go back to this We should never ever
start writing the query directly Just check first
from which table we want Because Manager is
in many tables at least in two tables
Okay So never ever started going And you
know start writing Query first go through the
the ER diagram and let's say what we
want to find out We want to find
out the departments which are I D departments
So where do you think they're department will
be I t department means I must be
eggs and names So we're looking at the
department name here So we're going to use
their department table here and the column is
department name and we want all the department's
I T Related which have no managers So
the other condition we have to put it
We're manager ideas None Okay So what How
will we do it Department name So we
want I can be anywhere in the department
name so let me just quickly before I
run that equity I have to do some
investigation so I will go department name from
department Okay I'll just run this Kuwaiti just
to see what are the different values off
department name are So you see administration marketing
purchasing There is an I T department If
you scroll down there will be I support
I helped us So all these departments are
there If you know this is the list
and all the departments for I t starts
with idee You can write it like this
with department name like I d percentage you
can either Right 80% Did you can also
write percentage idee percentage So doesn't matter right
now we're only having 33 department less driving
percentage here also Okay now is the problem
benefits Has I be in it Okay so
you can't just blindly right Percentage idea right
Do you have to first look at your
data that what the type of data you
have what are the values in the data
After having a look at it you will
obviously find out that benefits We're not looking
for benefits we're looking for ideally only and
all departments start with idee only So you
will remove this percentage and then you only
warned these departments and they have also asked
us that Tell me all the departments which
do not have a manager Okay so what
other condition You will put their See how
my writing indentations manager I d is no
Okay So you will get to department cited
the park Nice Well right now there are
no managers so it's not like there is
a query and used just talked according it
You have to first investigate it a little
bit Look at the schema Where is the
table Where is that column Try to build
the structure of the query in your mind
Then look for the daytime The department name
if it's ICTY Hardest ideal departments look like
is the idea that the end is the
idea in the beginning Okay then drive with
percentage I 80% a See the data that
you're getting is good or not If you
would know it's not good Try mortifying it
and then build your query like that Okay
Your expertise should be a step by step
Doing things were going very simple equities Later
on we'll be doing a little more complicated
So it's the It's the process that going
into your mind how to obey the Kuwaiti
step by step I even I'm not able
to obey the query in the first cool
I trying a simple one than add anchor
as bare condition that add another condition That's
how you build it Okay You will never
be able to ride the query very first
time correctly You have to do some research
and then you will come up with a
quick Yes it's again the regarding the regarding
the concurred functionally So we want to bring
the bona fide certificate for an employee employee
I d 123 Okay I'll come Depend Total
So I'm there is a long string here
So what I do I'm just copping it
and I want to type something like that
So how will I start I'll slot Start
with select con cared And then I'll blindly
put everything Whatever I have copied here later
on I will change it Sorry Okay So
can care This is to certify that full
name Let me try this like this with
employee I d And then I will put
I will close they could comma And then
what is the employee I d the name
off the column employee I d And then
I will put a space here Otherwise it
will be just put you know join toe
this then again comma See how I'm building
it And then again this double court and
then space is working as And then I'll
put close the string here comma And then
I will have the job i d column
and then comma And then again courts than
space in department And then close it comma
and then department I d Goma Then space
since joined it What is the joint Big
higher did Goma and then again courts his
horror and Noel grass ality is this And
then I will put court comma and then
gross ality What was gross salary Gross salary
was celery plus celery in do commission commission
B C D and ah commission be city
and let's remove this move does on comma
and then from employees there Employee I d
is equal to 123 Is it 123 is
there so Oh sorry Ah gun Okay So
how will you do it for limb We
can put Koncak first name calma because I
want to display the full name also with
the space okay Yes Yes This is your
right So first name comma space comma Last
name Comma What do you think Selecting kid
This is to certify that first name last
name with employee I d This is working
as this job I d in department since
higher did his or her annual gross salary
is this much and ah let's make it
Let's give it a name certificate from employees
were employees Ideas will do 123 Good to
go Let's try it Okay Yes because 123
is ah you know they're the commission Percentage
must be none Everybody knows why we got
no Because commission percentage for the employee i
d 123 is No So that's why the
whole thing it has converted into No So
let me just um commended out Let me
run it for all the employees Let's see
how it works Okay so you get the
null everywhere so let me order by 30
ticket so that all the north tunnel will
comes above I hope you're getting whatever Do
we have covered all of these yesterday Okay
So you will get something like this is
to certify that William Smith Okay with with
employee I d So I Mr Space here
kiss So this is to certify the billions
Smith employee I d 1 71 is working
as s a wrap in department It be
since this his or her and no glow
across salary is this Today we'll talk about
how to change this four digit you know
four decimal places to two decimal places and
formatted properly We'll talk about that but whatever
can captain it They I have asked us
to do We have done that good enough
Okay And ah the last one is it
No tent right Concussion list Employee I d
First name and their gross sell Reasonable monthly
gross salary is greater than 12,000 Show Least
two highest gross salaries in the output Okay
so let's try this one Sure list employee
I d forced him And gross salary is
salary plus commission percentage Okay So quickly control
is select employees I d Call Mom What
else Um already first name and gross salary
First name comma Ah celery plus celery in
tow Commission percentage on Let me give it
the name Gross salary Okay From employees Okay
So what else they want Let's imply the
festival Gross Alice whose monthly gross salary is
greater than 12,000 Okay Ah Where Monthly Gross
celery So I have to cut based here
I can't use the alias is greater than
12,000 And what else Sure leads to highest
gross salary So we have to order by
what Least two highest it means in ascending
order So order by Okay So you're getting
all the employees The first name and the
highest one is 19 600 all off these
and they're greater than 12,000 So we are
getting rid off nuts Um but the problem
with this is this probably there is a
person Yes as his option as his optional
Okay now the problem What is the problem
with this Can anybody think about it What
is the problem with this result By default
it should doing It's not doing it Okay
I still I don't know why it doesn't
like Okay so let's change it Remove the
alias Don't not removed Alias Somehow it's not
liking the one with courts So let's try
this one order by gross celery Yes Now
it's working It doesn't like the one with
the with the blank with the space in
it OK but the problem with this is
let's say there is a person who is
having more than 12,000 salary but no commission
percentage So what will happen That person will
appear here or not Because even if I
mining 16,000 my name is not because my
commission percentage waas No not zero none Okay
so it had made the whole thing So
the problem is the cell I would have
been covered it to never Let me tell
you this thing only so how we can
get rid off this null values What I've
warned that wherever commission percentage is no change
it to zero So I still get the
people with $15,000 But now as a commission
percentage so that the how to write it
So it's a very simple function in the
commission percentage I will write if No Okay
I'm writing it If commission percentages No please
make it Okay Celery plus salary into if
the commission percentages no make it zero If
it is not know then use the commission
percentage only Okay I'm going to do the
same thing here So do you understand What
is the meaning off If not if Mel
means if the first para meter is no
redonda second perimeter If the first perimeter is
not no return it as it as it
is So if there I'm having commission percentage
as know what will this return Zero Anybody
having a commission Percentages 10% What We live
return Okay so the function the name off
the function is if none same function We
have a Nautica I think the name there
is envy L So just make a note
in article It's envy l in si my
sickle It is if both are doing the
same NVs Yes Okay let's try to run
into that See if it works or not
Okay so it has worked So even if
there as there So remember there were 12
rows before there were 12 rows before Now
how many rows are there 16 Because there
were a few employees who were me more
than 12,000 But because their commission percentage was
no it was converting the whole salary to
know So these are small small things that
we need to think about then modify it
According No the problem is with aliases that
you cannot use alias in uh in the
very close Sorry That is a whole different
thing You know you're using the organization's database
They will not let you change their database
They will give you a little bit Only
access most of the time It's not possible
But you can you know just type it
like that and then get it It's on
in the memory We have not returned anywhere
Whenever you run this query it will run
run time And then it really turned up
reserved back It is not storing in any
table or anything It is not going back
and changing the table Please remember as a
data analyst they will not let you change
any day Tough select statement can never ever
changed data in the database It's everything is
on the fly Okay so just remember that
Yes In bed close That's the functionality of
skills No no her alias You know Alias
doesn't work in where clause that's for sure
It works in order by but in order
By also we just tried If it has
a space in it then it doesn't work
If it doesn't have a space then it
is working It depends on you Totally depends
on you Use it I'm not using whatever
practice you choose No no no no I
will never give you another As is optional
You can give it for readability purpose But
if you don't give it that is also
fine Yes OK Talk to me Talk to
me Yes Yes Huh If I don't give
gross salary I'm removing it Okay Ah this
will not work because I have removed it
Let me say I give you Give it
three Okay And then I don't it So
it is creating this weird looking column name
But that is OK Yes Uh tell me
having you it's working OK with the double
courts It's not working It's the single quarters
working OK everybody I put double courts That's
why there was a problem If you put
single course it will work No it's working
No it's not working It's not looking Okay
So don't worry We have other options Ways
to do the same thing So I'll just
do this thing I think that later If
there is something on the board then I
otherwise ever send individual query I'll take it
later Okay So I think anything else today
we're gonna do many interesting things and I
need your full attention without any Mr Benn's
off Okay so two days I have uploaded
this PPT again People come back I'm gonna
switch other sequence off Whatever reason in the
PPT join we're gonna talk about that was
after the lunch I don't want to talk
about joints after lunch because I know you
will be a little sleepy So let's start
when you're fresh the joints and later on
we'll do the first part Okay so then
now we have only joined Did that We
have selected data only from one table Okay
so we have only selected data from one
table Now we're gonna do that We can
We're gonna get data from multiple Tabor's and
most off the time 90% of the time
we joined table and get data It's really
10% of the time that we only get
data from one table Normally 90% of the
time we get data from 2345 many tables
and for our requirements Okay so now let's
say how will be joined How will be
joined papers in order to join papers we
need to have The first requirement is that
we need to have some common columns some
common columns for in those two papers Okay
Ben it's a common column It doesn't mean
the column name should be exactly safe But
from the logical point of view from the
business point off you you There is two
columns in each table which have same values
and which can those two tables can be
joined on Okay for example let's take employees
and department people We know that employees is
working in a department and we have got
created a master table off department and a
foreign key here so that we can't put
any department i d Here It should always
come from that So we know that department
idea is the column which is common between
these two tables So there never be joining
two tables first In our mind we should
be clear about on which column we have
to join Okay and again the column name
can be saved which is in our in
our in this scenario but it can be
different also doesn't met them Okay as far
as their values are logically making sense to
join we're good to go Okay so how
will be joined So let's say this is
my employee table Unless it this is the
department people So what will happen when we
joined B get data which is common in
those two papers What does it mean that
let's say I have ah employees with department
then this is my employee I have another
employee Let's say situation right now Let's say
that employees has not been assigned any department
He's a new joining And then there is
a department table We have been department number
as finance 20 as saves So when we
joined those two tables and again this is
the department I D And the sister department
idly what do you think Logical Logically What
data should I get If we want to
join these two tables how many rows will
we get What we will get So what
will happen How does it work internally Let's
look at it It goes here It finds
this It tries tojoin this to all the
values off their department and see what is
its match There is the match The match
is here So what It will return It
will return for us A meat then and
finance So we're done with this room It
go to the next room It checked Now
his here is not here Now Here Now
Here Now there is no no here So
the done it then starts from this thing
Okay It matches then is matched here It
will again check that then is already this
record is already in The results were fine
It checks this 20 says it matches 20
with anything Anything There is nothing there It
will leave 20 So what will be the
answer The answer is only one true This
is how internally it works So you build
get data That's why I have drawn this
guy Graham whatever is in common Whatever matches
only backing will be returned Okay If we
have more values here which there is no
match in the stable those will be ignored
Similar way If they're more records here there
is no match Those will not be that
Is that normal Functioning All the joint Okay
again There is some variation We talk about
those later But this is the normal join
in sq it Okay so this concept again
you know people join is a very very
important concept So please make sure that you
get this Listen to what I'm saying So
this this is clear that only the matching
rose only the intersection off this to sex
will be little Now coming back toe How
will be right The simplex off it Okay
the Kuwaiti is give me give employees name
I long with there Department name So tell
me how I will get this result Whatever
I have taught you till now Can we
get this result Using those techniques was saying
yes how we do it I'm asking for
employing May along with department name not the
department I d I want to see it
department named this person is in which department
Not the i d Because I cannot be
that you know um into it if they
can maintain random numbers Also I'm interested in
which department they are working So the problem
here is that board off these columns are
in different peoples which are those tables employ
and department So somehow we need to know
how tojoin these two papers in order to
get this information Okay so although we do
it we'll start simply People do select force
Name last name calma Department name This is
simple This issue we want these to these
three columns now comes from till now We
were putting 11 table in the front close
Now we will start putting two tables along
with the joint condition So please uh look
Look at this and quickly what we'll do
we'll start with employees It's be Palazzo Put
somewhere department and we could join these How
will be joined We will put a special
key word here Call in the joint again
This is optional So if you read somebody
scored Probably they have not given in there
because by default lives in there OK but
as a start you should try to put
that it is then in a joint in
That means we only want to get this
inner common data Okay so in that is
optional But I argue Do put that initially
Okay so this is OK We have been
one part off it but the other parties
know on which call them to join Okay
on which column toe joined these two tables
that the sin taxes on Okay we can
put employees dot department idea is a good
toe department or department i d but usually
what we do we do want to write
this big name off neighbors again and again
So what do we do We give them
aliases so I'll give employees alias as E
I'll give department and Alias s be departments
Yes you know Please um mind you know
sometimes get computer It's department So even if
peace make sure that this is for the
concept clearing If I have misspelled something to
please take care of that Okay on Leave
it right on end off department I d
is equal toe be dot That's it Him
They can be a foreign key relationship Physically
there is foreign key relationship But there will
be a situation where there is no foreign
key relationship Are still If you say the
date eyes same in both the columns and
you want to join those and get some
information Still you can do it SQL will
not ah stop you off joining weird columns
It will give you were desserts But you
you can try anything you can write eat
or department ideas It will toe the daughter
location I d You can do that also
It will not stop you to do that
It will not give another It will just
give some weird results which doesn't make any
sense Okay so but enjoining we need two
things One thing we need is which two
tables to join And the second thing we
need this on which column they need to
be joined So we were gonna cover both
So here we give that we want to
join these two tables and then it will
last on which columns you want tojoin So
we will give There is a department i
d in employee table Also I have just
given them an alias name here E means
employees d means departments So we will put
on E door department I d Is equal
to Tito If you put only Bill here
it will probably editor it out Okay that
you have not given the on statement with
it Yeah but it will give you weird
results which will not make any sense So
huh join keyword is necessary I'll talk about
that also But it a lot It will
what But did not give you the right
results So yes Okay hold on Just get
the simple thing I'll come to everything complicated
later Then you will You will tell me
ma'am Please stop Don't tell us and they're
tell me us or anything else So just
one by one Okay so I want everybody
to grasp this concept Advanced things I'll talk
about later How to do it Manager i
d how to do outre join how to
do other things were things But first get
the very very basic thing because I find
my students struggle with this concept a lot
So I want to spend quite a bit
off time on doing the simple things first
I don't want you to court all eyes
here You can take north in your notebook
but nothing more than that And see the
indentation how I'm writing Please use the same
type off indentation It will increase the readability
We have joined to tape us and look
at the output It saw Beautiful first name
last name and the department Not somebody thought
some cord Is this clear Because it's a
very important step people and you will confuse
later on So you have to fully you
have to fully understand this thing Okay Inner
joint will give you all the Foreman results
So now let's try um we'll try different
things and then we will do a few
more examples Okay so somebody was saying Let's
say we don't give this It will give
another and somebody saying Let's not even join
it So let's give it Oh you can
give inner join And let's say if yes
OK so what I did I have given
in the joint I can give Com also
here without the joint condition Anybody noticing before
we were getting Harmonie Rose 106 How many
we're getting now 2000 Also because there is
a Liberte have said to 1000 let me
change it to 50,000 and let me run
it again So it 89 rules So what
happened here happened here in SQL If you
do not give the joint condition what it
will do so it will do ah cross
join and what is across join I'll give
you a very simple example Let's say table
one Values are 10 2030 and let's say
table two and let's say the values are
102 103 100 Let's say these are two
papers and we have to join them somehow
But we have not given the joint condition
what will happen SQL What will do They
will create a cross product cross join Cartesian
product please Remember these names cross join cross
product Cartesian product all the same thing what
it will do It will randomly without even
thinking Take this role Beat that for all
the rules in the other table So it's
been put Ben 110 210 310 400 Then
start with this 20 122 100 so and
it will give you a huge amount of
data bitch have no link toe each other
So we're getting every employee connected toe every
department now Oh that this I have made
created a comment I made it a command
It's not executing So this is the way
to Commander I don't want to remove this
court because I'm going to use it again
So just for time being I'm commending it
out by putting two hyphens and a space
because I told you not for joint You
need two things for joint You need two
things One is the condition and one is
that what type of joint you're doing So
we have given it that we're doing inner
join But we have a toy that on
which column tojoin so it doesn't have the
full information If it doesn't have the full
information what did we do It will just
do cross joined by default So if you're
getting a lot of data somehow just check
your joints are good or not Yes So
the question here is that I am joining
it on First of all I can answer
that Is this thing clear Okay And we
can do the same thing sometimes like this
Also instead of doing cross inner join If
we write us join it will do the
exact same thing If I put instead of
cross joint Kamal So it will also do
the exact same thing creating that Cartesian product
Okay so why joining You have to be
very careful If you're joined Condition is not
good It will give you veer data It
will not give you another and you are
at lost spending days and asked to figure
out where did we go wrong Okay Cartesian
product Why we needed Sometimes we need it
actually to do that by to create a
bulk off data Let's say I have repent
equity and I want to find this performance
So what I do I create a beer
table with 100,000 rows and run that query
on it just to check its performance Only
their cross join is useful or get people
talk to me Same Okay then also the
same result Okay I'm gonna try that But
all the combination permutation you can try it
later So let's across joined We give the
exact same thing to wait 89 Rose Let's
drive with the doing this So now it
is You know even if had given it
is giving you the right data The cross
join in the joint That happened You whatever
you do the main thing is this thing
The main thing is the joint condition So
it's you know whenever you have condition is
given even if it has done cross join
it is still applying that condition on cross
join It has given you the bulk off
data but it has a 38 out Because
you have also given the joint condition No
no no no What Any joint Please don't
do that Please use inner joint That is
the concept This is just a weird thing
that has happened But you don't want to
court like that They would throw you out
of the organization that you're trying to confuse
people just to check that SQL is working
on North No Okay so I come toe
that we'll talk about later but right now
okay just just concentrate here The right thing
to do it the right thing to do
with this You can play with it trying
different combinations It will give you some beer
Taters Some not were data but this is
the right way off doing it Okay So
faith ring is done on this Five hour
is clear because late around for innards join
the village was out there Join also And
I'll tell you the functionality off that same
syntax only in a joint will change to
a local joint And then what does it
happen We'll talk about that But first understand
this Do not jump the gun Okay so
let's try one more example Uh let's go
here Okay I want to find out I
want to find out all the departments Ah
all the departments in city Ah what are
the different cities You must have played with
data What The city is there humbling Okay
so tell me all the departments which are
in City Berlin How will we doing First
of all can be do it from one
paper I want the department names all the
department names in Berlin Can we do it
from one table No Which two tables We
need location and department and what we want
We warned department name Calma Cindy Okay So
please help me I think this query So
what we'll do from location and s alias
and B s alias What else in that
join then on l dot What is common
in both hitting department is not common Department
is not at all They're in the locations
Any dog location I d is equal toe
be dark Location I What else City is
a cool Brazil is a country Clear Let's
extend Yes Okay So what we're doing We
want to find out or departments which are
in city but Tokyo So we will look
at the department paper and were not able
to find city in it So I will
not be able to find all the departments
off Tokyo from this paper I need to
look for a table the test to be
in it and I figure out locations has
city in it So somehow I have to
join this table Bid this table I want
department name and I want City from here
So I have to join these two tables
Another thing is that I on which key
I have to join So I look at
that These are joined on a core monkey
court location idee Okay The common key is
location I d And then we will just
use the simplex which I have told you
we will lose select department name Commerce City
from one paper Give it Alias name Inner
joint department D on a Lord location I
did This is the common key between them
and I put our condition also their city
is equal toe talk You let me quickly
called this because I'll extend this later I'm
going slow because you know I've warned you
to understand this perfectly OK people please maintain
a dick Your please locations Okay so administration
is in Seattle Marketing is in Toronto These
other department where the head offices in these
cities Okay so Okay so let's try some
Some more things with it So what will
happen You gonna tell me if I write
it here like this huh Thes please read
the other column Location I d in feely
listed m biggest What does it mean How
it is in their boards the tables Espuelas
asking Tell me from which table do you
want And for us it doesn't make any
difference I just need to give it an
a name So I have to give it
like l dot location I d So whenever
you get the center and biggest column is
amigos it means that the one column that
you are selecting or using in your Kuwaiti
it is in two tables and you have
to specify from which table to use And
in that scenario we can use any paper
It will give that exact same result But
you have to give it done Alias name
toe prefix it Okay let's move forward So
let's say I want to find out all
departments which are in country us Now let
tell me what to do I won't give
me all departments The chapter head office in
country us means the location is in The
location is in us And the country idea
is here But I want the country name
us United States That is a country named
United States for us Whatever Okay so we're
gonna learn how to join three tables Now
control is select star from Okay So us
country ideas us So let's say United States
Okay so we want to find out I
don't know about the country idea I just
want to find out all the department in
country United States off America How will we
do it Okay so I'm now gonna beat
you how to join the three tables now
So look at this Index please We gonna
port We have joined to table These are
absolutely fine Right So I'm gonna put parentheses
here So what will happen now This becomes
my one paper As soon as I put
Prentice's on it it has become my one
paper Okay Now I have to join this
paper now with another table What is that
Another table Country So this is my location
table Because location is already there in this
table So I'm done with the location I
have to now join on on with the
country So I'll write I start from here
only So this is one paper I will
write inner joy countries I'll give it another
alias See And what next on si dot
country i d is equal to l dawg
country I d I'll hurt country name here
and I really do where country name is
equal to United States off America is You
can do that I'm just making it a
logical thing You know I'm just putting Prentice's
in it But still all these things that
l and Eddie are visible here No no
no no That's not huggable This one is
already defined So you What are you doing
You're taking this whole thing and making it
was one table and then joining it toe
another table on something and then you know
you're getting that isn't this is how this
in Texas Yes Huh No I don't think
so select all No no no work I
think so Little book But logical thing is
to do it that again to make it
more readable Okay we can do many many
veer things here but please try to do
whatever this the guidelines or whatever makes sense
OK so let's try this I'm gonna put
this in parenthesis I'm gonna do inner join
countries Ah I'm gonna do on l dot
ah dot country i d is equal to
see dot country i d bed gun tree
Name s D called do united This is
again very important thing 90% of the time
we do this Only you can talk about
cross join Try this try their rights are
working But most of the time 90% of
the time we do this inner joy Joining
many tables escalates Pretty flexible You can remove
the parenthesis and probably then also book But
this is the right way to do it
for readability Purpose Yes with the help off
Okay So what I'll do instead of explaining
it Let official you that schema So what
is happening here We're joining We have to
find out What are the departments in country
name United States off America So we'll start
from him Okay we've been joined this We
cannot join directly to department because there is
no link The link here is from here
so we will figure it out We have
to join countries with locations first and then
locations with departments Then we will get the
data What we want There is no sequence
off joining You can join in any sequence
you conjoined country locations first and then with
departments You conjoined department location first then with
country So there is no restriction off the
sequence OK but what We have been here
We have fast joined these two tables using
this from statement locations l inner join departments
D on So this was clear You know
whenever we joined we need to give two
things the name off the papers and on
which thing you want to join So this
one came the first one and then we
have to join with countries also So what
we have done I'm considering this as one
table and I'm repeating the same sinks again
So I'm repeating inner joy again I'm repeating
the name off the table again It's countries
now and I'm repeating on this thing exact
same day Now it will join all the
three tables for me Okay And when we
joined all the three papers now I can
pick and choose any columns from here from
here from here What they might give you
will give you the data bitches Which is
the common That is why this intersection It
will give me only departments which are in
country United States off American Nothing else on
Lee Those locations where the country is United
States off America only the departments were The
country is United States of America Only the
common keep only the common rose Nothing else
So if there is a department in Berlin
it will not show it to me Okay
It's there is a country named India It
will not show that result to me because
I have put bets on 3 May musical
to unite in America's Only the common things
will be for it Okay so this is
the way we will join all our tables
and it will only give you the common
things between them Let me show you another
example And let's see if we can join
these Okay so this these are two papers
again One is the employee paper and one
is the department neighbor So tell me if
I joined these two papers inner join How
many rules I will get spent some time
First tell me what is the common key
huh So now tell me how many rules
you will get And because when we joined
were inner join it will only look at
the common common rose So we will get
our department I d is 12 So there
is a department I did Well no So
across 11 11 is here a pig pen
Tennis Here take 12 12 is in order
So we will only get Edwin and Stevens
data along with their department at Venice in
marketing And Stephen isn't If I joined these
tables and do not give the on statement
do not give the joint statement How many
rows will I get if I do If
I don't give a joint state man and
do across joined how many rows are you
Get 16 So Harry will be repeated four
times Harry will have I t department marketing
sources shipping and when will be repeated four
times Stephen will be repeated four times Eric
will be repeated prototypes Okay But when we
do in their joint you legal common things
which are matching 11 is matching here then
is matching hill because we're joining this Grow
this car Love it This column These are
two different tables and I'm joining this column
with this column When you join internally it
checks and matches the date stuff No I'm
not getting it So there is no wealth
here You're thinking about Primerica and for and
keep perspective Yes That rule has not been
applied here This is just some random data
for you to understand How the joint books
Yes Is something wrong here If it would
have been ah foreign key here we would
have never been able to allow allow toe
and 12 here But the key is near
that I have shown this exactly just so
that you know how the joint books But
again I Delius Yes That doesn't Yes that
is logically crib You joined organization six months
back Your ideas 100 You joined the I
T department I joined it today before for
me Many employees have joined different different apartments
So today my number is 115 employee I
d But still I will be joined toe
the I T department which is spent Okay
so that doesn't make it No joint can
be done not primary keys A concept separate
concept for the validation off our table But
joint can be done on any key until
unless logically there is a sense off joining
them So prime American foreign key concept is
there to make the data robust But joint
can be done on any case even if
their primary or foreign doesn't matter until unless
the logic says they're joining these two columns
makes sense So there is no restriction I
told you know you conjoined department I David
Salvi Nobody is going to stop you System
will not give you another but it's up
to you That's weird expertise That's where the
domain knowledge comes into picture Yes those will
come later so we're gonna do a lot
of joint Today's off Those will commit okay
so clear people inner join again Very very
important You're saying this is fine but when
I'll give you query you would have to
join 45 papers and then you know you're
you know your blank what happened And don't
worry about outre Joyner many other joints 90%
of the time only the inner joint book
Okay one more thing I want to tell
you is that this is how you gonna
ford from now on for joint But this
is the new playoff Joining tables When I
started my career I started my career as
an article developer only I'm a certified oracle
developer Clear some exams So SQL is you
know I've been involved in SQL for pretty
long time This is the latest method off
the SQL has standardize that Now you have
to join like this only before this there
waas a different method I'm just gonna show
you so that sometimes you go into an
organization and you have to maintain somebody else
called Okay so you should know that joint
can be done in this way Also it
was used to done in old way Okay
it's not very different It's a similar way
But you should know that also but from
now you have to do programming using this
syntax only because this is now the standardize
one So we want to display employing department
The same exact thing that we have done
Look at the first Syntex now is it
Are you familiar with that Is that Does
that look OK This is what exactly we
did Okay this is that and and and
size standards So that's why I want you
to record it like this Only But before
that we heard this type off joint which
was again You're simpler than this but again
No We have to switch on this one
Select employee I d select statement the same
from you Do not put inner join here
You just put a comma here and the
joint condition instead of putting it in on
you Put it in there Close So if
you see somebody scored joining like that don't
get scared that it's known joining properly This
is the oil the way off joining But
they have come up with this New standards
are going on From now on We should
use the scandal Okay Prospectus Simple right there
Instead of putting on we're doing bad This
is equal to do this Clear Okay so
now let's move Does the inner outer joints
okay And let me tell you the concept
off it So cross join everybody understood Right
Joining each table with each Robitaille each table
OK so inner Join the common thing Look
at the left out of joint It left
out joint We warned the foreman things also
but we warn the paper is all data
also so there doesn't need arise why we
need to do this thing So you manager
tells me asked you gimme report or gimme
data off all the employees and their departments
Okay But I also want the department's Oh
let's say I also warned the employees who
has not bean assigned toe Is that possible
that it's possible Let's say a database the
laws that the person has joined today he's
the training and we're not sure in which
department he will go So right now his
department I d is none Okay lets see
if we have data like this Select start
from employees How will I find out if
there is an employee with the null department
I d We're department I d is Kimberly
Ground Okay that's the person who doesn't have
our department So the man and it is
asking give me all employees okay along with
their departments And also give me employees which
do not have a department yet Okay If
we do inner join if we don't know
normal inner join what we're missing What will
be miss Yes we will miss Kimberly Because
when we do join and there is a
null in the joint there's Esca will not
pick that record Right So we will miss
Kimberly Joy Kimberly Grand But if you want
to include him also But there is no
corresponding by Lou in the department In that
scenario what we will do we will use
the left outer join or the outer joint
for nothing So if you look at it
this is my employee table This is my
department table I warned up common things also
But I also want an employee who is
not in the apartment Understood What is an
out Rajoy out there joining us I want
data all day Tough Rhonda from one people
doesn't make matter It's in the other table
or not That table will get preference that
give me all the time the stay But
have you joining it to other table But
I want to get all the records from
the stable Doesn't matter if it is in
the other people or not Understood and left
Outer Join means whatever is in the left
hand side and how will be called it
So we will cord something like this Celek
Uh first name last name De Bachmann Graham
Employees Okay B But man baby on he
dog The five meant I d We have
done that exact same same thing before The
only what we used to put it here
People we used to put inner join Okay
between give us a foreman thing now we
will pork Okay So what will happen The
table at the left hand side building and
reference So even if there is the savages
not being joined in department stable still it
will give you the all the employees type
it also select first name coma last name
coma department name from employees Let's try the
inner joint first depart hman de I'm typing
it for you people so that you understand
I'm typing it again and again Okay I'm
running it Ah So how many records I'm
getting I'm getting 106 rows Okay let's check
if there is Kimberly There are not okay
I am be Kimberly is not dead Okay
Because Kimberly does did not have Ah doesn't
have ah department so energy and will only
give us common things Okay which are there
In the both the table So now let
me change it to left Outer joint So
how many doors should I get Tell me
Huh So there's only one person we just
checked Kimberly So we will get 107 rose
from this And if I look for Kimberly
I think both will book But I want
you to put the left for no left
out to join I think it revoked Books
escalates flexible enough It will I think give
the same results 107 But you're later on
You can remove all these things I'm teaching
you the first time That's why I'm I'm
you know Ah pushing you to write in
the right way the first time later on
you human Yoon expert you will just you
know not care about inner not care about
out left joy and joy And you use
that left inner join I don't think there
is a need to get a ligament ended
I think I don't know You can try
the combination Okay I'm coming to that coming
to the but left out Resigned everybody Understood
Okay this is a little complicated concepts of
please pay attention That youthfully grasping the same
thing that fitted for left out Yes Okay
that by we have to do it out
of work Because the first that is a
syntax Okay but the violated outer join that
we need to understand we have did outer
joined because we want all data off employees
Doesn't matter that weather then the Nash doing
department or not huh No no intersection Then
you Yes So we will give be warned
this part abortion also and be born This
funnels and the right out of joint is
exactly the same way But the opposite So
here we don't want this be born This
one Okay As you will not need the
right out of joint we can just change
the position off the tables and can get
go with the left outer join only But
let's say we will do the right out
o j So what is the example off
that again we we take the same example
we want all the department and they can
be departments where no employee has been you
know assigned So in that scenario what we
will do we will put right out to
join him and what it will do It
will give me all the departments whether there
are any employees there are not along with
all the employees also the common one We
definitely come Okay But along with that the
departments will also come So let's first see
if there are any department which do not
have a employee How will be check No
we can't check it like Okay let's try
to run it So let's run it with
inner join first again 106 rows Okay so
let's see if there are any departments which
are having no employ OK so let's run
it again So via have come up with
100 and 22 rows and you see contracting
Nope Employees there I helped us and no
see I support So all these departments are
there Where there is no employee has been
signed Okay so what we did here right
out of joint means the right table Got
preference Give me everything from right Tabor Okay
Doesn't matter whether it is in the left
People are not along with the common things
also So you see you get all the
department names even if there are no employees
assigned So that department and you will get
no in the blank spaces So what we
have then we have given preference tool the
department stable so department table will get all
the data Doesn't matter if it is in
the employ table along with the common data
Yeah so it depends on the position If
you write things left out for joining the
people that you want the whole table that
you want should be on the left side
And if you're doing right now Rajoy the
table which is on the right side is
the one which you want fully But you
can have only left outer join and can
go away with the ports The things you
know you can put the same thing You
can go right left outer join move departments
there who employees there It will give you
the exact same thing But inner join Muhammed
so you can go No keys May Department
tidy employees maybe or department I did Department
may be common data Who knows You don't
exact match Only then it will come So
when they show you this this diagram So
this area is only where Department ideas 10
here Department ideas Then here Only those rules
will come Only the common thing If it's
a melon there it will not come if
Except now And we want Oh take it
No department ideas That huh OK I show
you that So let me show you people
one more thing Let's also type in he
dot department I d comma the dart department
I d So tell me people what will
happen will get no But what will be
indeed our department I d the actual value
off contracting Okay for this I d off
contracting that will come in the deed or
department i d But either department idea is
not which is true to living in the
table Also it's like that Lena in employee
paper there is no department contracting for that
The value is no OK but is the
deed or department having value contracting with department
I did 1 90 How I got him
out for joined cart ahead So what happened
L values hair toe licking evil as a
Nordic aga What Got him left out Join
career ahead So you decided Values the tiger
Kimberly Because there was a nun in the
department I d in the employee table But
no in that No no deed or department
I didn't want 90 year the door department
data 1 90 Hey there's no way to
find out We eat or department ideas No
Until unless we join those two papers The
rule is not dead if you do it
If you're not give anything There's no raw
the whole now in the table Employees Maybury
ronel honey Today a mechanical keyboard department honey
But there is no rule like Nalanda for
this department break Okay What is the time
Okay One last thing So let's say if
I do in their join here What do
you think E dot department idea indeed Our
department I d What will be only the
common one Okay Only the goldman one There
will be no Nell anywhere if you are
doing in a joint So what What were
the What was the homework yesterday the 10
or 16 queries in my file Remember they
were around 16 2016 17 Okay And besides
that there was a quiz And then there
was this the bluesy P as well lest
their take home exercise So in all of
these if you have an equity just tell
me and I will take you through Ah
you know whatever 20 you were not able
to understand are not able to do Okay
Yes Take home exercise Question three Write a
query to display the employee I d celery
and salary range off employees as tier one
tier two s for the range or bring
out goodbye Those two years Okay So what
do you think Which command are which operator
on how we do it remember Because now
they're e conditions We did something similar Something's
a military before So anything self less than
then here to three So we will use
the case statement So the problem is with
this index off the case Okay so let's
quickly run It recognises the employee I the
salary Okay so let's try to do it
Select employees I d comus celery comma will
start the case statement here case And uh
then celery is less than 5000 Then I
think the year one and then celery is
celery between 5002 tens housing Then do your
true when celery greater than 10,000 isn't like
that Then two year three and then you
have to end the case statements so end
and then you can give it Ah name
salary range And then from in employees And
what else was there ordering output by these
tears So Or Dubai sanity range Okay In
the case statement please remember that you have
to type When When When Multiple times for
each condition And the other rule is that
after then whatever you are doing the data
type off that should be same So I
don't think you can hit tier one tier
two Tier three All our string were displaying
the strings Okay I don't think you can
do are numeric one nomadic other string things
like that So those are some of the
rules that we need to follow when doing
the case Uh K statements Let's try to
run it So if you see less than
5000 it's giving tier one If you scroll
down to year two And then if you
scroll down than tier three Okay so K
statement When we have multiple things to do
If there are only two missions what operator
way could have used if if this then
this and then coma Did a three argue
Put your phone on silent mode Um so
thoroughly two conditions You can use the F
operator but when there are more than two
you have to go to case it starts
with case than then and then you can
write it like back Ah so this and
statement is telling the system that K state
mess has ended Huh Whenever you use case
there should be an end of it We
just gave a defense he name It's just
an alias It's just an alias name that
we have given advise if you don't give
it it gonna print here The column Name
s case When something like that I don't
want to do it I am giving it
a fancy name called Salary in It's an
alias name we can add Let's try case
Okay So we can try a few more
things I'm writing another case here Let me
see water the job id's select distinct a
job I d thrown and Loy's Okay so
let me show you a few more things
we can do So Case let's say job
I D C This is a little different
So I'm after the case I'm writing the
column name and then I'm writing Then job
I d is isi account Okay When Joe
by these is Is he content right accountant
Okay then it is a c manager then
Manager else we can also use else here
else Ah Okay So here there are two
ways of using case one I am putting
an expression here If it is true then
it's gonna print that And there is another
method I'm giving the column name after case
and then I'm doing when this column name
is equal to this print This if it
is equal toe this print this if it
does not equal to any off these for
everything else bring other And then I'm ending
the case statement and I'm giving it a
name and let's say what does it give
us So it is giving us this person's
job I d must have been something else
so other other other But if somebody is
accountant so account ideas So he is getting
as a counter to nest company we have
to mention up huh So what is happening
so logically Think what it is doing We're
saying if men job I d is equal
toe this or this drink that So we
can go use the case They friend like
this also we can make job I d
from here and we can right here case
when job I d equal to a C
account job I d equal to a c
managers When we're doing equal equal things we
need not to do it in the vent
statement we can write the column named above
and then it will take care off It
would just do it equally critical in the
Penn State So it's another method writing when
we have greater Then between Then we have
to write it inside the vent because now
we're doing different different operations But then we
know job I did We always check for
the equal operator You can put the job
I d above and then write it like
thistles Okay with K statement it's a very
powerful statement We use it many times Yes
there isn't Beck said check the equal Did
you take the edit message What Did you
sing the game then Dio there is no
come after celery comma after celery You know
you when you start kiss This is another
column that you are starting So there should
be a common before the case statement So
did you see here That is a on
my hill And there should be a comma
here Also Que statement When you start it's
starting a new column Okay so take it
off the whole month Understood Gay statement Okay
Don't you know Try You could take a
picture Are you know wait for it Um
tell me Anything else are no down in
your notebook somewhere you can try it later
Let's move ahead Any Anything else in all
those homework Think about logically Does it make
sense What exactly are you trying to do
Only then you can put group by You
can't just put group by like that Tell
me a scenario where you want to do
something and then we will try to see
if we can put group here Uh then
you If you want to know how many
columns are there in a table What is
the command everyone describe So you'd use a
describe they believe and it will tell you
how many columns are there in the table
here I'm breaking columns on the fly So
I don't know what is the need to
get the number of column You Congar likely
see it from here That there are four
columns there s in Is your on your
equity No If you running any query you
look at the select statement How many columns
are in the select statement Those many columns
are there in the query It's going to
return those Millie condoms I focus should be
to get some information about the data Not
that much Work it about How many columns
are brews are there is to get the
data and then look at the data What
It's trying to tell us Okay Right The
query to display names off employees whose first
name as well as last name ends with
balls Okay so let's try to do that
We're gonna use off function I did not
mention it yesterday but there is a function
called left and right Okay so let's try
it Select first name What is the question
Display Name of liver First name as well
as the last name ends with bobbles Okay
Ends with bubbles Select first name coma Last
name Wrong employees There Right off First name
Go on Mom One in it Yeah I
Oh you and right off So you have
left and right Columns Functions left will give
you the record of the characters from the
left and right will give you from the
right So if you do this sub string
will be difficult Because to get the last
corrector you have to first find out the
length and then do lank minus one so
you can do it that way but it
because it's a little complicated but this will
give you so it's exhibit bobble It ends
with bubble It will be moved The first
the last character Okay so let me show
you here Right off First name calling Mom
one So let's see What does it print
it Spending the last character So if I
say to here it's gonna print the last
two characters three it's gonna print the last
three characters Okay Right is a function you
don't like There's a function called Lord Substrate
in string Similarly there is a function core
left and right But does they do they
give us If you pass a string to
it and then you have to pass a
back it's gonna go in the string and
give the left most two directors So what
will be answered here be by So when
you do write off by 10 and then
give three what's gonna get done Hot Egil
So these are two more functions that we
can do So somebody asked Sub string Let's
try that also Yes Huh What did you
say It's what I'm doing We born all
the first names Which eggs with Volver What
How many levels are there Five So I'm
putting those volatiles in a list Ok e
i o u and I'm asking it Ask
Excuse that Dollfus Name one It means corrector
office name It means first name should end
with available and then I'm chick taking after
getting that off last corrector ask director in
e i o u If yes it's gonna
turn through and it's gonna fetch that record
for me Otherwise it's not gonna fetch that
record So you see first name last correct
It is I is Involve us That's why
it is patching up this record How many
vocals are there Okay so think about it
How will we do it How many walls
are there in a string in string instinct
We give you that position at the first
First uh the first thing that it finds
Okay you need to check There is function
court fined and find all something like that
Okay that will give you that You have
found it So check on the help Something
called find and find All Okay So we
have to use the same thing using sub
string How will we do it Let's try
it Sub string help It is true Only
paired it with values So let's try this
How will be U sub string So for
sub string we have to do first name
and then we have to find out the
len cough OK minus minus one will give
you that Okay we can try that Uh
minus one So sub string At what you
can do it Find the lantern Do can
do length minus one Also So the sub
string is also minus one is also giving
you the last sciatic Okay so sub string
and let we can use all right here
we can use to find toe pass the
strength No Okay so yesterday we covered many
single row functions my hero functions And also
we covered the joints OK so we're going
to do more exercises on joints today Probably
the left and right outer join also So
uh tell me more Targa My ego functions
that we did yesterday My feet or functions
count and then account men Max Some average
median So these are the commonly used But
there I'm sure there will be more off
those But those are the mean commonly used
group functions are the material function We also
call them I agree Good functions Okay Any
other concept from yesterday You want me to
Ah repeat I'll be happy to do that
Otherwise we'll start with today's stoppings Okay Again
I'm I'm again Um I'm asking you to
practice more and more off SQL The more
you do the better you will become The
more editors you get tryingto us trust spend
some time On what diarist thing Try to
figure it out what it is telling us
and then accordingly make changes It's all about
practice The more you do the better you
will become at this Okay so let me
start fitted to this topic sent So we're
gonna do set up with actors today a
little bit about those how to use up
quickly So we're gonna writes Upgrade is also
that is in the first half the day
and in the second part off the day
we will start with the D l and
G M l Anybody remembers what ideas What
would the commands huh Create all Terry name
So today we're gonna do do those also
not in very detailed Because I told you
as a data analyst you'll hardly do that
But at least if the court is returned
using those commanders should be able to understand
that what these are doing And we will
also do the d m l insert a
bit and the league okay You of that
So that is today's agenda So let me
start with the set operations You remember Add
math classes What are the set operations that
we didn't match Yes So the sect operators
are union on We can apply the same
operators in our queries Also How we will
see Let's first say what are these set
operators again Minus and intersect Okay I want
you to go close your laptop All eyes
Here You can have your entire life to
court This will Time will not come Okay
So minus and intersect They are not available
in my seek Also will not be able
to run the queries But I will tell
you the concept because I don't know on
which database Chewable Khan Later Oracle DB two
sequel server So please you should know the
concept off Buying a cent intersect Okay so
let's start with this example Ah we were
a company who were making or selling right
And this was our table off products We
were selling Nokia Samsung HP and Nicole And
I don't know the products really off these
companies And recently we acquired another company same
type of company you know we acquired that
company So now I'm having let's say two
papers One was in their database And what
waas in our big this So Florida at
one table and product through people So when
I do now the business question is give
me the list off all the products that
we were selling in the new company after
the merger So now tell me what are
total products that we're selling So which operator
So now these two papers we have not
more the tables yet I just want to
write a query so that I can tell
my manager that these are the products that
we're selling right now So how will we
do with which operator we will use So
when they do union what results we will
get We will get Nokia Samsung it be
gone Okay Will we get Samsung again Because
we're doing Union Union will remove their duplicates
So we will get then LG Dell Apple
and Placed ish And if I do you
know then we will get even the duplicates
We will get 123456789 10 10 records back
Okay so that is a basic difference between
union and unity in all Okay next come
minus What does minus two Okay Before they
use minus Let's say what is a business
question Somebody asked me Okay let's through the
Intersect First give me the products which were
selling as a common You know they were
also selling We were also selling Okay so
then which operator we will use intersect And
what will be the desire Samsung and each
which are common which are common So union
is you can say we are covering all
okay Union Avea repeating Even if they are
they are duplicates inter sectors again Only this
portion Okay And then what This minus minus
is minuses Only this back Only this And
what is the business question Business question is
give me all the products list off all
the products which we are selling Okay but
they were not selling that Okay so give
me the products Which river Settling Okay I
want just want to fight What are the
extra products that we as an oil company
were selling Okay I'm not interested in what
they were selling I'm interested What My as
a parent company What are the products we
were selling which they were not selling so
that you know I will probably um in
their staff Also regarding you know these products
that we're selling so that is probably the
requirement Okay so there we will use minus
So this is my oil company This is
my new company acquired company Okay So what
I want to find out about to find
a rich products that we were selling and
they were not selling Okay so I will
not cover this common part Okay In bad
scenario B will use minus OK Minus is
is like doing minus and maths Okay How
do we do it Let's say we do
a B C D minus B e You
don't just a mask on that So we
will do minus off this So Evan come
here B B B will back See we
come here the DB cut So we will
get This is the same scenario No no
I'm only interested in this minus this Okay
this portion minus this portion So I'm really
interested in finding out data from here after
minus subtracting this chrome it So I'm not
interested in what the other companies selling so
minus will only take care off this portion
and then subtract whatever the common things in
the other thing And then give me that
huh So if we do Okay So we
will both face So let's say we do
Product one minus producto What will be the
result Nokia and Nick on Okay so no
can Nick When we were selling that they
were not selling Now the business question is
tell me which products are other company was
selling which were not feeling what then we
will do We will do product to minus
product one water The products Mr Other company
was selling which we were not selling Probably
the business reason is we want to study
those products Should we want to keep selling
those Or should be you know just to
stop selling those things Okay So in that
scenario we will do product to minus product
one And the answer will be LG Dell
apple and placed issues Okay so that is
the concept behind these operators Okay I'll show
you the working off union I can show
you minus and intersect but you get the
idea You know what it is Okay so
let's see this Okay First of all is
this clear Union Union all It can be
an interview question all over that in Union
and Union oil Why do we used minus
You can give the same examples in the
interview also Okay Okay Union Union O minus
and into sick No but it will work
in Oracle and DB two You should know
the concept You know you concertante we will
do the union in uh in uh in
my secret Okay So a few rules also
about union used to combine the result set
off two or more select statements removing duplicates
Each select statement within the union must have
the same number of columns So that is
a rule for union statement What it is
if I am doing select That's hypothetically and
Heidi Last name from M one union So
I left employee I D name Let's say
there is another table called M two I'll
be able to do union only in two
conditions One condition is the number off columns
defined in the first select statement should be
exactly same as the number of columns selected
in the second statement So two columns selected
here to hear if three here three here
Okay so I know three here and to
hear no union will not work Okay Because
It's looking for the exact match because that
finance select statement will give the columns at
the same number of columns which are in
both Okay so you can't do that Other
rule is that the data type data type
off the corresponding columns should match What does
it mean if Empire is indigent here it
had employee I d should also be interested
If this is text here this should also
be text If this is Day eight here
this should also be dick Okay so union
or in fact all set operators will only
work if in the to select statements the
number of columns should be same and their
data types should be saying otherwise they will
not work What time can be different But
the number of columns and their data here
I'm writing biting by my writing in plot
It will as long as there are both
her text Okay so that is the condition
that the union and Union all are all
set Operators required similar data types and must
be in the same order More than two
queries can be club using more than one
union statement So if there are three product
papers we can go from product one union
from product to union from product three and
so union can be used many times Okay
Union can be used many times with many
sickle many select statements So you consider union
will only cover bonds and union all will
cover Also cover the duplicates Okay So quickly
to get the union off all types of
many So we will do this Kuwaiti duplicating
means if um remember that uh product example
So what is the difference between union and
union all when I do union So see
Samsung is here also And Samsung is here
also When I know union all it will
give me Samsung twice because it was there
also it was there also But when I
do union it will remove the duplicates and
give me some Some Libyans That is the
difference All right which column needs to be
returned Okay So like in this example what
columns Village time Only two columns Villager huh
Doesn't matters What will be my statement here
My statement here will be something like this
Select broad duck name product one leg raw
duck name from So my results will be
product name Only one column will be returned
And with all the day huh So let's
try that So you know it looks for
removing duplicates It looks for the whole room
Okay so if that will not make any
sense from the business point of view But
let's say if we do this get bigger
T i d Is this category 80 and
get degree idea Now what will be designed
by doing union and removing duplicates It checks
the whole thing So my desires will be
Van Nokia So Samsung and then one Samsung
again work Look then what does it Okay
the different None of it Huh So three
hb three h people only comes once because
the whole Roy's it's looking for duplicates Three
hp here and three HB here So when
we do union all it will show three
hp twice when you know union It was
shown Libyans what I'm trying to do Is
it for the whole caller of the columns
Remove duplicates What We have duplicates in table
one Okay you're just thinking randomly From the
business point of view it will not make
sense because this is a primary But if
it happens Union will still remove If we're
having to Samsung here again Union will remove
Only give us once to Simpson Okay so
it doesn't think it does think that this
is my results Sec from where it has
come I don't care Just remove the duplicates
in the desert sect But about that Samson
That is the man you know Really What
We're talking about the union functionality So in
the same scenario there are many products of
products We will try to get the union
off those papers Also again you understand up
functionality of union So if you yes up
a product we will do the same thing
And accordingly Whatever the date is the same
rule will apply Okay Come back We will
come back So let's do one example using
our it just a month So let's say
our company has organized a you know be
mounting in Goa Okay So help me Our
company has organized a team outing in Goa
and only managers are allowed to go Okay
Only managers are allowed full Now as an
HR manager I'm thinking who should we take
with us Okay so if you look at
that just came I will open it No
So there are If you look at my
skin my I'm starting my schema and we
have two types off managers in our company
OK what are those two types So one
list I can get it Whoever's job title
whoever's job title is having mgr Let let's
look at the let's look at the job
fighters Okay So public accountant Accounting manager All
off that let let me look at only
the job I d Let's say we will
do with job ideally Okay so can you
see anybody ends with the job I danced
with M JIA That is probably a manager
Okay then This one is also a manager
and uh and May and okay And man
is also manager So anybody's job I d
ending with man or m JIA We are
considering Graham as managers so that this one
list Okay then another person comes Okay My
job title is not manager but I'm still
managing teams And how will you check that
Because if you go to the employee table
my manager my ideas in the manager I
d okay because you know I was a
team leader My they did not change my
job tighter but still a managing people So
I should go to look Okay So now
they are having two types off managers in
our system and I want a complete list
off All the people who are job idea
ends with M JIA or Amir Those will
also go And all the people who are
having their employee I d in the manager
I d they will also No As I
I asked my database person toe fetch me
a list Now I'm thinking as a data
based developer how will I get that list
in one Quitting Okay What I Lubitsch operator
should I use them Union I will fetch
data Ah for the job I diva job
by these manager on a man and then
a union net with the with the query
Where the employee I d in this manager
Right So let's try to ride that quality
Okay Celek employee i d Let's write it
for job I d First employee i d
First name comma Last name Common job I
d From employees there How the late I
think Okay that job I d like percentage
underscore m A and oh job I d
like was sent ditch M G I Yes
you Yes Uh no no In looks for
I think the exact value you cannot 4%
in link in Okay so you have to
use like you have to use like you
can't use in here Okay let me run
it Okay so 10 Nancy's going then is
going Mattoo is going So how many people
are going 14 rose OK so 14 managers
will go but again I told you that
there are few people who are not managers
but they're still managing teams So we have
to take them also with us So we
will Right Union Celek Okay so I have
to Exactly Yes No without uh oh yes
I'm just making sure that it will work
with this also But again you know our
convention is underscore men If there is a
future there is anyone by mistake The man
is it's not manager but some other think
but with understood I know that this is
definitely a manager but it will work with
that also I'm just being more precise here
Select employee I d So please help me
building this query How will I get that
That this employee I d is in manager
I d Okay so today I'm not gonna
use inner join because I think now we
got used to off it so I'm only
don't gonna use joint Okay So employees e
join employees I m Okay So what we
want Let's draw a pictorial picture This is
my employee paper and my m i d
And this is my manager I d So
find out First name last name off this
manager So I create a copy This is
my M piety This is first name last
name and I joined it here so that
I can find the first name Last name
off All the managers clear the suddenly did
yesterday Only self joined So m and on
Can you tell me what What should I
join on Okay I'm just missing the inner
thing because I think now you got used
to off join means in a joint Okay
so I need to prefix these Otherwise it
will This will throw me another So imply
I d What should I prefix it with
Remember I want the employee I D this
person Not this person Okay I won't employees
idea of this person So I will do
m dot employee I d Okay and then
job i d Job idea probably m dot
only Okay Manager's job idea And then I
m dark First name and then I m
dot last name So now can you see
I'm having the four columns in this query
and the same exact four columns here The
data type is also should be same Only
then I will be able to a run
this query Let's do order by ordered by
Let's say do first name Okay so I
think we will be able to do this
I think first name will not work here
Only the order work So let's say order
by two Do is that first name So
everybody should be clear about this squarely We're
covering many concept in this We're covering cells
Join here We are covering union here so
please take a look at this Kuwaiti And
if there is any question just tell me
Okay so let's see how many people we
have to take to go up So we
have to take 18 people before there were
14 90 now for many people have been
added So this is an I T program
But Alexander But he's managing a team So
even if the job title is not manager
we're still taking him with us because he's
managing a teen He is a manager off
a few employees Okay so go back to
the story going to go up We're taking
our managers to go up OK so I
want to find a stuff my all the
managers one day is to look for in
the employee table job I d I know
that anybody's job I'd be ending with men
or manager is a manager That is the
company policy How we have defined the job
ID's So my list welcome directly from here
which I have returned in my as my
first query Okay but now somebody tells me
you're not taking me but I'm still managing
a team I'm still a manager but my
my job I d is not a manager
Okay so I have to take care of
them also Now how will I find out
those people who are managing other people and
still the job I d is not manager
So what I do I goto this table
for all the employees I will find out
their managers all the employees Just give me
the manager's So basically it is giving like
this just a second So select distinct manager
i d from employees I have to take
these people also all of them Okay I
have to take these people also along with
me Okay And how will I find out
Number their names I have toe these air
The manager ID's OK so what I have
to do So I have to find out
their names Their names are returned here in
the first name Last name But I cannot
radically use this one I have to make
a copy off this table here And then
I have to join this manager i d
With that employed I d to find out
the first name and last name So that's
why we have blue cells join Because I'm
not interested in employees First name last name
I'm interested in managers First name last name
for that We're cloning the stable You're joining
manager I did with the employee I d
like and then I'm fetching back it bus
name and employees But isn't it 18 Ruth
No not new people Probably their their job
I d is also manager and the other
one so we can put that check if
you want that game you live their manager
is their ideas not in manager and no
So we can check that also their job
I d not like this And we're job
I d not like this We're job I
d not like this and Oh okay I'm
doing it wrong I'm checking the employee job
I d I have to again do the
self trying to check this manager's job I
d So that's what I'm doing wrong here
So But I'm telling you here because we're
not putting a check for that manager ideas
job I d Right now it is giving
me all the managers so those managers can
have the job idea as man and manager
also So if you want to check only
the people who are not having job i
d As men and manager then we have
to again to a cell's joint like the
one that I have done and then check
this M door your body and your job
I did not like this I was checking
this job I d So that's why it
was not giving Okay Okay Not getting okay
so let's try this Give me all the
managers whose name is not ending with manager
And so we will go employees e join
employees And okay And this manager idea is
coming from E dark manager I d everybody
clear till here I'm gonna put on e
dog manager I d is equal to m
dot employee i d and the wear clothes
would go like bad en dot job I
d Okay there m dot job I did
not like this and m dot job I
d not like this So I got only
four Ruth Okay so these are the people
who are managing but their job i d
is not Doesn't have manager are men in
it So along with those 14 people that
we have selected you know these 14 people
we have also take these four people so
total becomes 18 Okay In order this man
is job I d We have to perverts
like this We can use the job I
d off This implied That's why I had
to do herself join again in this scenario
is true But there can be people who
are not managing I'm a technical manager Okay
I'm no I don't like to manage people
I am still you know a senior level
person I don't want to manage anybody So
my man I will not have my i
d in any very editor I'd but I'm
still in technical manager My job title is
technical manager so I want to hold a
whore Okay So that's why you can't in
this scenario Yes that's true But you can't
think about like this that the data will
always be like that Okay You have to
write a foolproof up equity Okay clear So
this was the example of union and union
union and union Or you could ride union
all and can check if we give you
a few redundant data Probably Okay So Union
union all you the same way you can
do intersect and ah Okay So what will
interested I will onto interstate because of Lord
Ball But think about it logically If I
do intersect how many people it will return
me What is the logic What will be
the lodge who were taken to Goa If
we use Intersect Huh All the managers who
are having men and manager in the job
tighter as they're less were actually managing So
if my budget is low I'll only take
those those people I think 14 people okay
so intersect with sober separate purpose and Unionville
server Oh there are ways of doing it
using sub queries So I'll tell you that
when we lose some queries But uh you
know believe me my SQL is only for
us to learn Okay this packages three off
course But most of the companies where you
go to work they'll hardly use my sq
They built by a nice looking package for
you know for a huge amount And you
will definitely work on db a mess which
are you know having all these facilities No
no no article is so expensive is the
most expensive I d be amiss Yeah the
virtual boxes You only have you know the
frank think I need and you can save
your data But when the companies go in
production think about Citibank You're ableto own the
software there So many odd It happens that
you should own the software that assured properly
backup recovery They can't use this freely available
software Imagine your bank using freely available software's
okay so we can base used think Do
they intersect Using some complete It's up equities
But we did not do it You should
know them Meaning off intersect And that you
will bill whatever you're using in your life
will be able to do that Okay so
that was about set operating this Yes Uh
in Excel Festival us again There were first
name It doesn't matter Whatever the result comes
Really You can take first name here because
the names can be different in the phone
Query and other queries always go by the
numbers In union you have to go by
the numbers If it's an individual queries Inpex
ever I look at it later Look at
this Soviet Having this is M Okay this
is M This is E that manager information
I will into this table So I made
um dot employee i d and m dot
first name and M Okay Okay people come
back again No according we gonna now entering
into a territories which is a little scary
Okay so I want your full attention a
difficult fatality So we gonna put our whole
focus on this sub queries using query between
equity Okay so I tell you how to
write sub queries You know I'll write a
peach of the Syntex but think I want
you to focus on the thought process So
whenever we try to solve a problem similar
way you know writing us sequel First spend
five minutes thinking about what exactly we're trying
to do on a piece off paper build
a little blueprint or what the query should
do Over tables were touching without worrying about
the syntax That is the art that you
need to learn Syntex Anybody can learn Okay
so please focus on the part Process sick
for five minutes Think What is this doing
What is this asking this question is And
how will I be able Tow attempt If
you think it like that your life will
be very very easy when we're your handling
sub Quickly So we will do it Many
example And let's see how this will go
Okay Some queries Equity with inequity It's also
called inner Kuwaiti or Mestre Quickie also called
sometimes the outer queries called parent quickly inequities
called a child quickly Another name usually added
in the very close off the SQL but
can be in other closes Also 90% of
the time we use up equity in the
bear close but there are metres off Putting
a sub query in from close also will
not focus on that will focus on the
bedclothes used when it is known how to
search for a value using a select statement
but do not know the exact value What
does it mean We will see Okay some
queries are on alternate way off Returning data
from my people papers So in SQL if
we are asking for some data there is
no one way of doing it that you
need to remember Okay so you say that
your answer is not matching with the other
person but the results are saying because they
are My people met thirds off doing the
same thing in sq it's up to you
which matter too big So most of the
things that we do with sub queries we
can do it by joining tables also but
again by the U sub queries because the
logic that sub queries used it's the same
logic as we think Okay we first find
this is the step one off solving this
problem So for step one will write a
subcommittee This is a step stool Well right
now the main quickly So this is how
our thought process works So subways are important
so that our data is very much readable
at God is very much readable weaken related
to how we think about solving a problem
Okay So how does this what is the
main quickie So let's look at this Victor
Employees have salaries greater than Smith said That
is the question that we need to answer
Okay So what is the first thing that
comes to your mind when you look at
this question What does Okay so what This
mid celery So if I can find whatis
Smith Selvi I can use that thing into
the to find out which employees have pretty
have getting greater than Smith's So what I'll
do I'll write a sub query for Smith
Celery and then I'll put that sub query
in the where clause and then write a
main query Give me all the employees There's
the It's more than Smith's selling So let's
try toe right This query Smith is just
I have taken a name Probably start in
the database so even See Okay So what
This myth salary How will we find out
Tell me Tell me They're bored Select salary
from employees Let's say Smith is the first
name First name is the cool too So
this is that sub quickie So what will
go I will put the pin parenthesis and
what I want Now give me all the
employees which is salaries greater than so I
will Pork salary Great Did that Okay then
what I'll put I put there Then I'll
start putting select employee I d Whatever you
warn First name last name Graham employees If
it returns no value than the whole of
expression will become No And we will not
get anything from the queen Yes but don't
go into the Zynga If this is the
case we have to do it But let's
just first try to understand the concept off
some quickie Okay so this is I should
be our part process going forward Whatever queries
you'll write those will Lord being a simple
one Okay Those really very complicated one and
your thought process should be tryingto take one
part off it right It's a query And
then start building the main query or the
other things on that equity There him We
have those other things But let's try to
understand nothing We received there to smell things
like that but okay so probably for doing
that we have to probably create up another
clone off employees and then you know try
to fetch their tough from from one clone
The query should be Frost Smith So there
are some complicated with if you want I
can write it during the break But when
the question comes this is what our comes
to our mind and we gonna cord like
that also And it's not necessary that a
query which is returned in sub query but
always hair can merit and using joints there
are fewer I'll give you an example but
this is the best scenario best readable court
that comes to mind when we have a
question like that Okay clear about how to
write sub queries Okay so few rules that
sub query inner query execute before the main
query So what will happen here This query
will run first It will turn a value
that value will be stored in memory And
then this query will run That will use
that value for this quickly Okay this is
how internally it books The result off the
sub queries used by the main Kredi to
restrict the rose compared is inoperative So you
can use many things here less than greater
than equal Northey Cool and me all exists
So we will see about all of these
The They're single or operators and my people
rule operators will see all of these lists
calories off those employees who draw higher salary
then able So again let's try to run
this Grady A bell is I think there
in the database Select employee I D comma
first name calm Last name Actually we will
start with this select salary from employees That
last name is equal Do in bed 11,000
Okay now how will be using We will
write bear salary Good it through then Okay
So this is how beeville a query can
contain more than once A query A sub
query can also contain another sub quickie Okay
so you can nest it as many times
as you want No if you put North
P quarto salary nor people so so it
will give all the salaries except 11,000 That's
you Right then right The logic first right
The query and then show it to me
Donald alone Answer back Okay So you can
work with many permutation combination put Max said
But the logic should be so you know
the first thing should be what is a
business question Then you start with will be
the equity So you give me a scenario
A business question that I want to find
out the second highest salary Then us tell
you what are the different ways are finding
it should be used a sub query or
shoot a simple query will book Okay So
first think about what is a business question
and then even see how toe friend that
Okay so we're gonna do many sub queries
People please pay attention Not according on understanding
less employees who have seen job I D
S employed this but draw a higher salary
than her Don't Very It looks very difficult
Violated Couldn't do it step by step and
it's really Lord be difficult at the end
up it List employees who have same job
i d As employees night So let's focus
on this And I think Nyah Nyah is
ah last name Let's say I think of
the last name list employees who have job
ideas as employees die So first what we
have to find out job I d off
Please tell me the again Don't court concentrate
here Tell me the quickie So we want
all the employees who's job by the same
as this person So what will be put
here and then select list employees so I
can write employee I d first name last
him And then I just also want to
find out job i d Let's sit And
from implies now we have done half off
tall half off the problem What is that
The one but draw a higher salary than
her huh Higher salary then hurts What What
will be first find out No celery Okay
so So leg salary from employees there Last
name is a call to Nyah's and then
higher Then him higher than hurts so higher
then celery and I think we're good to
go And let's put yes competitors in Castaic
it So So We want to find out
all employees who are satisfying this condition and
who are satisfying this condition So this condition
is their job They should be Quito something
and their salary should be greater than something
we want to find out This is a
simple state majority If you remove this let
me put 100 here and let me put
10,000 here It's doing the exact same thing
Select Star from employees were Job I physical
200 salaries greater than 10,000 It's a simple
query that we started with but hey before
we used to hard court hardcore mean job
by physical 200 Now we are making it
Something is the wrong time You know any
for a person Let's say after a year
this person salary changes Let's said I know
his salary is 12,000 Okay so we could
have hard quarter to 12,000 but the benefit
off putting a sub queries then let's After
a year her salary changes and now the
logic will still remain good for my query
If I hard coded We have to go
back to program check Okay We have put
down IRS salary somewhere in the court Now
have salary has changed Let's change the court
We will remember It's the exact same quickly
that we have returned The very first quitting
select start from employees Where this This This
exact same huh No What I'll suggest you
concentrate here This you can vote later Look
you on a missile of this I definitely
answer I'll spend as I can Stay late
and answer You quit is But please people
concentrate here I know there are so many
combination permutations going on in your mind You
will have enough time to do all of
those things But let's just try to focus
here Whatever I'm teaching later on we'll have
Will do all of those things And I'll
not go home without answering each and every
query off yours Forward No we could have
done if the salary is equal Toe her
salary Okay if the question waas um who
have same job I d and draw same
salary as her If that would happen the
case What We could have done people I
could have done Joe Biden Comus ality Didn't
this and then job I d comma salary
put it in parenthesis is equal to this
So you can put my people columns and
conjoined with you know compared it with the
my people columns off this sub quickie But
here the problem was one was great Everyone
was equal so we could not do it
So an on the job you will get
equities like this nor distinct liquid is that
we were doing okay Probably more complicated than
this Okay so this quick and also very
10 using equal and joined versions So again
we're doing a self join and then writing
it like this Okay so do answer You
are ready What's your name Mishima So there's
the same query We can do so by
doing self joining Remember The first query also
told you we can do itself trying But
again you know somebody has to think about
this logic and it looks so complicated Cells
join itself is very complicated Okay so that's
why we goto This is what a heart
processes step by step Okay And all sorts
of queries can be converted into equal in
trying statements will definitely need some queries Okay
Clear Okay let's do on the next one
list departments whose minimum salary is more than
the minimum selling the off depart ment 50
list departments who's minimum salary is more than
the minimum salary off department 50 So what
is the first thing that comes to your
mind Minimum salary off department 50 to select
minimo salary Bram Employees Yes Bad department The
part man I d is a quarto list
departments So let's leg department idea If we
weren't named then we have to do inner
join Okay we'll do inner Joyner's No no
we'll see Will hold a needle of abatement
ticket Now what The list department whose minimum
salary is more than this Okay so minimum
salary is more than this more than Ben
Bad and then Minima family So now we
have used minimum salary in our query It's
an aggregate function Soto get aggregate to use
aggregate function What do we need And we're
doing the minimum salary off departments So we
will doe grew by group by salary grouping
is which we have sorting it out We're
sorting it out on department and finding the
minimum sorting out on department finding the minimum
salary e So group always on which group
you want to thought to find the aggregate
function So it will be department I'd be
Huh Huh Goodbye Okay so what We want
to find out We want to find out
the minimum salary off also What is the
logic So what I want to find out
Depart men accounts Minimum salary One was in
finance Sala de 1200 Find out the list
off all the men's Then I will check
each sell it the with this minimum salary
If this is great that this minimum salary
let's say for department 50 the minimum salary
is 500 So I have this list I
have this number So I will check this
number with this It's good to go I
print this Let's say finance keep minimum salaries
200 I will compare it with the 500
It's not good A lot printed and so
do it all the department Okay Yeah that's
a good question We are using minimum salary
OK And I told you that you cannot
use agree gate function in their clothes What
we do we will Yeah Having group by
at men I'd and then because we have
using department name here we have to do
from department B join employees Okay employees e
department D On whatever it is Let's try
to write it out because this becomes a
little complicated So let's say if it runs
or not I'll change the foreign Talagi But
it becomes to figure Okay so select many
Mom you can also type in if you
want up with me Minimum What is it
Minimum salary from employees That department I d
is equal to 50 2100 Okay then I
will put it in a bracket And then
we will put having minimum Sandri greater than
this Kuwaiti Take it off the indentation Also
it will make your equity very much readable
Group by department I d Okay from employees
Let's say I I learned a department name
later Okay Select department I d comma minimum
sanity Okay so I'll run it So these
are all the department who are having celery
More than 20 100 more set of numbers
not 2100 So the minimum salary is more
than 2100 So let's say I want toe
print the department name Also department name coma
employees join e join departments The on E
dart department I d Is a call to
d dot department I d And I have
to prefix department i d e dot department
idea Okay And run it again and not
me All these department when l was coming
before But when you join it with the
Arteta the table now will be gone No
So this is actually you write your queries
in real life you start from one thing
then you build on it Is this kid
this up Equity Yes Yes because uh this
will give the same results as that one
Because department idea and department name is there
is a 1 to 1 relationship but you
can give there is no problem Try to
give it so that you know later on
if there is an issue but they will
not be sure because department ideas a primary
key But still that's the best practice to
give that it will say that returned the
same result So you can put this Aquarian
Where Klaus also in having close Okay so
let's look at this Kuwaiti Okay people stop
40 I'll give you the presentation Do it
later Let's look at the last weary and
you're gonna tell me what is wrong with
it Select employee I d Last name from
employees were salary is equal to So we
want to find out all the employees who
are earning the minimum salary in each department
We want to find out that those employees
who are earning minimum the department's So what
is the business logic We want to find
out who we're paying the less probably they
have more tendency to leave if they can't
came Come to know that they're getting the
lesser the least salary So my mended wants
a list of all the employees Apartment varies
work earning the minimum salary in their department
So that is returned Select minimum salary from
employees group by department I D No that
is not the problem Yeah work So what
did you think There are 10 men How
many rows this query village done Ben And
what we're doing we are only comparing one
salary with equal to men things Is that
possible So what operator use if you want
to see So the problem with this is
that you can um you have to use
in here so we can use it Cueto
is only then 1 to 1 Combat isn't
otherwise for multi row thing You have to
use in You have to use any R
and exists Okay So if we right in
here they will give us those people who
are ending the minimum Okay so let's try
this one again People all eyes here Okay
If I don't it it will give an
address A query returns more than one roll
I will change it to in And these
are the four people who are getting the
minimum salary So in 90 department these two
people are earning the minimum salary and the
salary is 17,000 Yeah you're right Okay So
we will do another query to do the
exact same thing because we have no match
The department also Okay Very good course Very
good observations of it But right now just
understand that in in operation in what does
Indust If there is a list given by
the sub query it will check everything in
that list but very good observation And we'll
handle that situation when we talk about coordinated
security Okay so this is just some two
d There are equal toe greater than all
these operations that you can All these operators
that you can use in north in any
and all will be used when the sub
query returns multiple values Okay so let's try
this one more list employees who work in
countries other than us and UK So think
about it How will be doing there list
of employees who work in countries other than
us and UK So let's I'm gonna bear
the query here only Okay so you're gonna
help me select country I d From Let's
it countries that country I d not in
us Okay you keep Let's run it So
this is Ah I don't know Berlin Czechoslovakia
Denmark Czech Republic So there many countries So
we want to find out the employees who
are not working in us or UK So
how will we find employees So we have
in the country table So how many tables
we need to reverse We are in country
from country to locations from locations toe from
department employees Okay So Celek employees I did
comma first name common last name They're sorry
from employees Mm Join departments The on E
Dark employee I d is equal to d
dot department idea Okay And in a no
inner join join what locations on L dart
location I d is locations or location Huh
Is equal You don't Yes Okay And then
we don't need country Okay We don't need
country because the location I location has country
in it Okay So bad country I d
And Okay so these air that three people
who are working in who are working in
Canada who are working in Canada One is
working in Denmark Everybody else is working either
in US ordinance Yuk Okay so this is
how step by step you build your equity
There you go They have no joint country
table It's okay We we could have done
it the other way Instead of using country
I d From countries we could have used
it from location only Okay so go through
this query Eos It's doing exactly the same
thing I'm not printing here Uh so I'm
putting in and in twice So what is
happening Let's look at this We're finding out
all the location i d Their country idea
is not in us UK And then I'm
putting that in creating another sub query and
getting the department I d And then creating
another query where the department idea in So
they told you same thing can be done
Many of it is so heavy I think
with sub query within us up equity I'm
being okay So let's let's do it one
more time okay Celek locations I d there
Is it locations that um from locations Where
country I d in Not in you with
UKIP So what did we give me It
will give me I need not go to
country stable because country stable has country name
I can go Go with the idea only
So this will not country stable is not
required So huh So let's now find out
what other departments on this location I d
So I will Right there Location I d
in this and I am looking for the
reserves What other departments Huh So we can
do it this way also from departments okay
And then select department i d from department
So this is again I'm finding are instead
of joining together I'm fine doing it Step
by step Okay First I found all the
location I d So if you look at
this one what I'm doing for the u
S Are you can not us or UK
I'm finding location ideas from this location ideas
I'm finding all the department ideas and from
this I'm looking for give me all the
employees who are in these departments Okay so
now we have come up with department I
did Now we will look at where department
I d in select department idea Okay And
Then I will write from employees select employees
What is the problem with this query Yeah
how the problem is you cannot find department
name here or the country name here How
we live imply I did And then first
name were killed on something Go So this
is for this on and service for this
Okay again the three rows return But the
problem with this query is that you cannot
find country I d Here Okay But there
again that I'm telling you they're different base
off doing the same thing Okay Okay So
we're gonna make this okay good again today
A lot of revision today Here also hands
on exercises I have prepared a mixed worksheet
for you with the simple very simple commands
purse And then as you move along the
complexity will increase a little bit as we
have done You know first did a simple
select statements coming from one table Then we
did how tojoin two tables Then we did
how to apply the function single row function
and Monetario function Then we did how to
do some queries and uh sub queries Union
Union All all those things were going to
devise all of those today But first of
all any query from whatever we have covered
Till now we have done quizzes Three quizzes
three are in class that the Blue CP
exercises and three take home exercises And along
with that the three worksheets that I have
given you So in all of these is
there any equity And you feel you know
if you felt struggled working with some query
Any question any quiz question Feel free to
ask We will prop post are clear those
things and then we'll move What kind of
crazy is best used to create a report
Listen customer first customer including their associative purchase
order How many purchase order How many orders
you have Pleased that you go on flip
card you have purchased and orders there Right
So what I want to go I want
to do all the customers and there Um
I would join it with the order table
and then I'll get for this person How
many orders are pleased Okay So the right
answer will be equated with an outer join
So even if the person has not placed
in any order I still want to see
that buster Customer information Okay It's best to
create a report listing customers by the court
and including their associative purchase order numbers If
they have made any orders if they have
mean any orders then I will want to
see their orders If they have not mean
any order still are going to see the
customers How many customers are bad Okay so
it means that we want to give priority
to customer table It means we have to
do outre joined on the customer table so
that even if the person has not placed
an order in my own my website still
I want to see that custom So save
which query contains us in text editor you
can probably cut based it put it in
the workbench and then indented And then it
will be better for you to understand that
like these are very big queries Has anybody
done this one So the best Where is
toe Take this in the SQL workbench Okay
You don't have it Doesn't matter You have
database in or it's just gonna put it
in blue and black cutter in cording just
to see That s seriousness in text Editor
or not Okay Doesn't matter The data is
dead and we're not gonna fund it I'm
just putting it there for the better readability
purpose So like this where they looks okay
to me without even running it We're just
doing a select statement Their product i d
And we're writing some sub query in it
and we are matching the product I d
Hill And then let's let's so do this
one next cut base This one This also
looked good to me What we're doing we
adjust Ah uh you're just doing bad This
two things is in this to think so
here is also a bad hair is also
a bad So I think it looks okay
to me Yes it will book I don't
think there's innocent text editor in it The
syntax and it will come Okay way We
have seen the second one So this also
looked good to me Let's see the third
one you will see a glaring problem Okay
for the hard one So without even worrying
about are their tables What other tables If
you look at it just the structure off
this query Can you see any editor in
it like logic Vice That shouldn't be a
problem We can use the wear clothes in
This is a very close off this main
quickie and this is the okay So group
by is also there So that's fine We
have been many queries which are having read
and grew by two by moving dated yesterday
There's something else wrong here Okay But should
also not be a problem I can go
group by department I d and I can
do order by department idea Not a problem
I want you to look at this thing
and tell me what is the problem Yes
The problem is what are we comparing We're
comparing this column with output off this query
How many columns are here Only one created
by user And how many columns here too
Okay even if we're using in in requires
a list but we'll never be able to
compare one column with two columns Harvin's is
a schoolbook Okay remember in this Kuwaiti that
this quit even comparing this pad So this
column will be compared with this one This
column will be compared with this one Okay
but here we're only comparing one column with
two columns so that is a problem Okay
so try toe cut place in the bicycle
above bench try to invent it and then
see what is the problem with the queries
Can be used Reverse Gifford I don't know
I know I don't Him reverse check You
know they're coming up with new new functions
so I just want to check They are
have done anything in it Reverse in my
sq It huh So what does reverse do
if I give reverse ABC it's gonna give
me see a B so select reverse No
no it's a function So reverses that's gonna
grow function So if I give it any
string it's gonna reverse sick Okay so if
I write first name coma reverse soft First
name of from employees Okay so you'll see
it is reversing the strength It has nothing
to prove it Sorting were not reversing the
sort backs order by descending here we are
just simply using esa like we use you
know upper low upper case Lower case Similarly
we can use the reverse function Anything else
in the quiz Anything you're struggling with Yes
Question number five Which one in the quest
just check whether the sequence the same or
not sometimes are Sequence off for questions is
different So question number five is this one
Okay use the table for all the quizzes
below So this is a table that they
have given So this is some game table
There is some I d off that game
Then on I think on which day it
was played Okay this is the date And
then in which stadium and what was the
pain When I think these are football matches
so poor land and Germany I don't know
Okay so this is the data that we
have given on Then there are goals So
in this match I d is 1001 in
this match This team and this player has
I think uh game time the time off
the give the time of the game that
player has played Is it like that I
don't know Okay Okay When the goal was
called Okay so the gold time by this
person So in this match there were three
goals you know happen And in the 17th
minute 51st minute And that 82nd minute And
these are the players who have done that
goal And then what is this This is
the the court off the team So Poland
team the coaches This and the coaches are
this It's Greece Okay You want to find
the stadium there Player This Okay so you
want to find the state younger player This
has played probably Right So where is the
player Player is this and this is the
stadium Okay so we have to join which
tebas game and gold and on which key
we will join it on the I d
key Right So if you look at this
a gente has played in 1001 in the
name of the stadium Miss National Stadium Okay
so we've gonna join these two tables game
and gold so Ah game and go This
is a one on match i d So
the these Why not team i d But
first of all it's the ET Mangold Yeah
Six also Okay Who You joined the papers
Goal and e team So gold has who
has done a goal At what time and
e t miss the coach Right So you
joined the tables goal and e team in
the SQL statement indicate the list of column
names that may be selected in the select
line It's very easy So what does that
a goes What are the columns for this
match I'm so your match I d D
my d player Gold time Got it So
let the court with shows players their team
and amount of goals they scored against Greece
Okay so let's try this one It's again
difficult to read from this Let's try toe
move it to the SQL workbench and let's
then see if we are able to find
it What exactly it is doing Do you
think indentation make a difference while reading the
query Okay so what we're doing we want
to find out the team who has played
against Greece and how many goals they have
scored which shows players their team and the
amount of goes this court against Greece So
how will we find out Let's look at
it So we do not need E T
Do you Do you think we need a
team Um paper Because eating is only giving
us the courts name so way don't need
this one So this one is out off
the contest So let's say we're looking for
game and gold and game and gold We
want to find out against Greece So what
we want we warn always either Team one
is equal to Greece Our aim to physical
degrees right Only then the Greece has played
that match Okay so in the very condition
we want So we want Team one is
equipped to Greece on a theme two is
equal to Greece This is the one free
tradition revolt And then now let's look at
that Which player has go ahead Ah made
a goal against in that match So we
will find we're joined both the Sabres game
and goal So from game join goal on
Come But it's a common key on do
you dog game Mick ahead I d is
equal to gold Make ahead match I d
Okay so this will give us the combination
off everything and then be hair free Tear
it on how many of only the games
bear grease has played And then because I
want to find out for those matches What
were the players Okay So you can select
Let Okay What was the question So like
the wish Your players their team and the
amount off goals they scored against Greece players
So be happier And then what we want
we want Ah their team Okay so there
are two teams Okay But we can select
the player from that from the gold table
I think Right So team there Okay so
this uh again we have toe pick the
team I d from here from gold be
my day from gold and then we want
number off goes so we have to do
how well defined number Of course we have
to do count go dogs we can put
Is it a gorgeous ah gold time We
can put right count off Gordon times and
then on what thing we have to grow
by Huh So they're saying player has made
how many goes which shows players their team
and the amount off goals they scored against
Greece Okay so group buy plan And we
do not want those goals which have been
scored By whom By the Grease team We
only want us goals which have done against
Greece Okay so we have to put one
more faith that here then but Yep See
my day off Gold Okay there the be
my D off goal is not equal to
Greece Okay Because we only live one Those
players who have done a goal against Greece
not by the player off Greece So we
will exclude Nautique will do grief Okay so
quickly should look similar to this one So
now let's look at the queries the best
way to do it Okay so let's look
at this one This is the first one
What is the problem here The last quickie
Look at this and tell me competitive with
the with this one Ha It's so I
think we got the Jack party when the
first query is I think the correct But
let's look at the second one We just
want to be sure that we have picked
up the right one So let's look at
the second one What is the problem in
this Huh Team one is Greece Whatever What
happened If Team towards Greece team one was
Poland and team towards Greece Still the match
was played with Greece Right And we're not
counting those goals Are you able to see
the money is equal to grease It doesn't
say team toe can also be Greece So
it will not pick all the matches that
have been played by Greece So this is
there is a problem with this So this
kind Vida answer Let's just pick the hard
won also just to see yet Obilic a
legal a toga Guess it You double click
a little Goodbye huh We're looking for fallen
matches not of Greece matches So the outrightly
this is wrong So pick up the first
option I think that is the That is
the correct one But this is how you
solve it right It's not only looking at
it You have to think you have to
spend time do step by step thing and
then you will get dancer anything EDIs Enquist
Because they have not created the foreign keys
Okay so it's OK You just look at
it And people if we will be able
to see that how the tables are related
to each other Okay so I have done
the same thing Okay So even if the
foreign keys are not there So let's look
at it This is my main product table
where we're storing all the products and this
is a product class Class scored So there
is a product Last court here So this
looks like the list off all the project
class and were heavy This is the primary
key and looks like there is a foreign
key relationship here Okay so this is how
you go about it If the prime foreign
keys are not given based upon your domain
knowledge you figure it out Probably there is
a connection there And when you will join
you will join these two papers on product
class school So let's try to understand this
Be at selling We are are selling things
on Internet Let's severe flip card or of
any Amazon any company So we have a
table that has all of our products I
wrote that I be product description product class
court class code can be Is the 10
appliance is that grocery You know that can
be the class court off the product Product
price product quantity available Len Okay Probably the
products lend You know how big or how
small it is Lent bit height and then
big Okay so these are the information that
I have on my product Okay And then
I place an order Okay I have customers
So customer is this information online customer His
customer I D name first name last day
Email phone number customer creation date user name
customer gender All of these things That hip
Okay So can you tell me customer gender
is okay But customer name is where can
buy is back huh So they'll only defined
it Care one It means it's m or
F under six So that's why they have
defined the desk So this is my own
land customer And there must be a table
somewhere with joined my customer and my product
Can you tell me which is that Which
one is that Table customer places an order
that every place an order on a website
So which is the table which connect the
customer with the product No no name There's
only one paper that connects board online customer
and product product has a master table which
is product online Customer has a master table
which is online customers But there must be
when we per changed Order There must be
a table There were story This customer has
purchased this product This customer for that column
that Uh Which one you said No I
don't think it's Ah or dough or the
header Do we have product The neck are
probably Yes I think Yes Ah no Still
no customer I d there is the product
Cancel order I d So actually start one
paper It is actually two tables What is
happening then There is an entity call order
Okay And order can have many products in
it Let's say I order five products in
one order On flip card I order a
mobile phone Um let's say laptop like ordered
five things in it So there will be
one order created for me so that order
will be in the order Header So order
I d Today I have created it This
is my customer idea This is the order
date or the status expending ship Amen More
pay mended Okay So customer is connected to
severe is that there is a foreign key
between So where is the connection or the
head That has order I d as Ah
but I'm Ricky But voters Its foreign key
customer idea is the foreign key which is
connected to this table Okay now this is
my order Head Now I want to find
out what items I have ordered in this
particular order I d okay And that connection
is bad That connection is it or the
items how many items I have ordered in
this particular order number And I will have
five rows I will have one role in
order Header I will have probably five rows
in order items if I have purchased five
products in that order Okay So what is
the primary key off or the right terms
Primary key is or the I D and
product i D So for order number 100
I have purchased for the tidy P 100
p 200 ft 300 before and sores when
we have true columns as primary keep Or
do we call them composite primary key And
these this primary is also foreign key Okay
So order idea is a foreign key Coming
from this table and product idea is a
foreign key coming from this table Are you
people getting What are we talking about How
this thing is because it's very important to
understand this If you don't understand this will
not be able to join tables properly and
get the required data Even if the foreign
keys are not given its ok the based
upon are no less We can figure it
out How these tables are connected to each
other Okay so ordered items This table is
done We industry this We understood this This
this and let's now say And this also
and then the address Okay So where is
the address used Yes here it is So
what we're doing a customer can have many
addresses right Even when you are birds Office
address It's the home address Okay so this
is my master table This is the child
papers So one customer can have many of
addresses Okay so customer and address I think
No this one is simpler What It can
only have one address because address I d
is inside the customer paper So one customer
can have only one address According to the
state of this So every side is a
foreign key here and it is connected to
the address I d primary kids Okay everyone
okay And then So this is also done
Then we have two more called tables Let's
understand that God been in ship So then
be carton and ship Oh so huh Carson
I think will be connected So this there
is no garden Ideally missy Ah he had
it disconnected Okay So should put ideas Hill
So every order that be placed there is
a shipper I'd be attached to it But
says what is the shipper name Sheep A
phone number and shipper address And then I
think there is also the car tonight He
has not been used anywhere No it's OK
There is a table Probably They forgot toe
They created it to be used later We'll
see how we can use while doing these
Take home exercises Did you ever use captain
tables there Any exercise ready to use Carton
Okay so well said this is just some
table then reviews But we understood everything else
didn't way So the best way to deal
with this type of database get off topic
primp it and then probably mentally create the
foreign keys the relationships the lions and put
it in front off you And then it
will help you toe right All the queries
Okay No this one is just toe collapse
it and the open it and close it
This is for this of this one This
is a non key I think And this
is the key Remember it all Bar key
attributes and non key attributes So the yellow
wanna primary key and simple Vanna burn normal
keys And then there are some I think
blue color also right on some I think
they also I probably the normal not know
So that's why there are some blue bullets
Some are blue some are not right Can
you see the blue and not blue Right
so probably these are not metal keys Okay
now we have understood our database Now we
can start writing quickly So which Kuwaiti people
have many questions in the take home exercises
Okay First they come to that Lets don't
dare to first Those are simpler one Then
we'll go to day three I'm right Okay
Okay It's fun Six sixes Mohammadi HRC here
Okay so we'll do it What is the
average salary range So now the nursing every
celery range It doesn't mean that we gonna
take on average Okay What does it mean
They have explained every doesn't mean average It
means the friends between men and Max Salary
off all types managers and clerks So only
where the job title has manager or clerk
We want to find out the difference between
men and Max Entity So let's look at
the diagram Okay so where are the job
titles George Titles are in the jobs capers
and where it is a min salary and
maxillary We need not to use the aggregate
function Actually they're talking about the columns which
are named Min Salih and Max Ality okay
And we want to find their difference and
difference only for tightest which are having manager
or clerk in it Okay so how will
be right The Squiggy select Max Sala de
minus men salary as difference from jobs So
that is so I was just simply write
one query here and then I'll also type
job title So first big the simple quitting
then we will add were close order by
clause All other things Okay They have asked
for only four job fighters which are manager
and clerks So we don't want testing testing
I have a did We don't want public
accountant We don't want administrator assistant We only
want people who are having men What managers
and clerks So we will add Ah bedclothes
Job title like manager Oh job title like
look So we will only get managers and
clerks See accounting manager finance manager marketing purchasing
clerk and so on So if you see
sales manager is the one who are having
the maximum range means you can hire a
sales manager on a on a law salary
also and on a very high salary Also
the difference between men and Max is the
maximum I'm sorry to Rose It's OK You
know I'll check and make sure that I
think I added more data that we have
added more data later So that is the
reason why Yeah probably they have interpreted test
clerk and there is no clerk and manager
dresses No So this is fine Nine dross
right Equity to shore the count off cities
in all countries Other than this with more
than one city in the descending order of
country ideas Whenever something like this comes it
is very very confusing Okay so what is
the method of solving this type of problem
Do not even look at the rest of
the thing Only look at the first few
words right Equity to show the count off
cities in all countries Stop Okay count off
cities in all countries So how do you
think we'll solve this problem We want to
find the count off cities and we want
to show the country's OK so let's start
building this one Select Let's say country I
d comma count off cities and from the
year diagram We know that country I d
and cities are in the same uh in
the same paper What is the name of
the paper locations Okay so country I d
Come on top is if cities are city
from locations let me just run it This
is what I didn't I didn't expect I
wanted to find out Hurrican Cremate How many
cities are there Okay what did I do
wrong Hill or one Did I miss you
Huh Huh So yeah So the Countess find
there are 23 cities but the country I
it has not given me by country I
d So remember that picture we have toe
group on country Groupon country Find the number
of cities group on country find the number
of cities Okay so Even if this is
giving me data this is not correct Date
as you have to think and then add
because we want to do can provide eyes
number off cities So we will add group
by country I So Australia has won Brittain
had one Canada has one I don't know
Ch is Czechoslovakia Has to Devia miss after
this huh They decided Let me see Control
appointing Constructing I said Vico Jaggi concert See
my ahead Show me the tapes by locations
Medical City here Condoms sitting and location Country
I D head untreated select country I D
com account off city or MySpace door barking
locations locations tickets Renco then food Uh the
U K Mac millionaire Your version has Quinta
would run in Nicaragua Query Your check La
Guardia hair Tacky difficult group by needles So
which is a good thing Probably We'll get
We're not done We are only sore The
first part off the pretty sure the count
off cities in all countries Now there's three
the head other than us and UK So
what do have to do so we don't
need to do now other than us and
UK so we don't want you with We
don't do not want UK Okay so there
should be production dish preparation in the world
clothes or in the having close Okay Please
remember having should only have federation if it's
a federation on aggregate function the federation is
north on aggregate function What is the aggregate
function here Aggregate function ist count off city
If there is a federation off count off
city we will put it in the having
close It is a free sedation off country
and on the raw data means we do
not want those rose from the location stay
But their the name of the countries are
us or UK So please remember this concept
Okay What happened then There is a federation
off count only then even put it in
helping If there is a figuration or any
other column we will put it in the
very close So we will put it there
Country I d not in us You so
right now it is showing me 14 Rose
and I can see us and you get
three and four But if I run now
it will only return 12 rows and us
and you care not but it don't Okay
again we have not done Vidic let's look
at the Grady again with more than one
city I was like I am a little
bit more than one city huh We want
account Toby more than one city So we
want a federation now One count now there
will be Put this in heavy Okay so
we will write having count off city Great
that then what So now 12 Rose returns
All the tools will only the tools will
be left One will be gone Canada Czechoslovakia
It leads a pen Yes yes Name knows
you can either Writing country I d northen
us comma You care or you can write
country idee Northey call to us and all
country I did not equal to UK like
will not come by will you We use
life We know exactly it's us and UK
life will increase on makeup Query Very very
slow Okay so the last thing is now
they want to sort it some in some
manner What is that This is descending order
off country I d Okay so which is
pretty easy So we'll go order by country
I d and then descending So Japan will
come first and it lead then Czechoslovakia then
Canada So you should learn Not only the
syntax also learned the process off Doing problem
solving step by step line by line No
we have causing a condition on the country
I d okay And country I d We
have even we have done a grouping on
it But having made what will happen only
the things which are aggregated Okay either count
some men Max Only bad thing will go
in having for everything else For all of
the simple columns the federation will go in
the bear close We live in the Federation
is on aggregate function What is that great
function here Count not by group by You
have to think it like whenever the federation
is on the aggregate function and there are
only five agree git functions count men Mac
some average If there is a federation on
these five things that will go in having
otherwise it will come in the bed Close
Huh So there is a fixed sequence off
commands in SQL The first one is select
Second is strong heard Is there 14 group
by fifties having and sixes Order but and
limit always come at the end That is
the after order by you cannot change the
sequence off this it will not book Okay
the sequences hair only The number one is
the legs Second one is from hard One
is where Fort is group by fifties having
sixties order by seventh His limit bacon is
not bacon Yes Display all departments Name the
city where there are located along with the
manager name author departments Again we will only
look at a few words Display all department
names the city where these are located Okay
so all department name and in the cities
they are located So rich tool there But
we have to join Jacqui cake Next one
Which one take home exercise to question them
But then Okay right Equated to display details
of customer who have Gmail account and for
number consist of 77 Having you write consist
of 77 like percentage 77% get like percentage
77% of it did assimilate qualifying for names
which are having in it Similarly what does
No Do you have to join Manager I
d off department is equal to employee I
d off employees to get manager name men
department idea department make manager I D Hannah
We want to find out that manager's name
so that men and it is also an
employee Okay so we will join that manager
I d with the employee table and we'll
right be dot manager i d physical toe
e dot employees 80 and get the first
name Last name What does Take home three
You're looking at something You're done with this
one Okay take home three Which question Five
starts up Okay Right Acquitted to display the
product details for the class core product idea
description As for the falling criteria and sort
them in descending or the Ross category Okay
so that we will do if a category
is this increased the price by this Okay
so first of all let's say which table
we have to hit product details So it's
gonna be Where are my product Beatus Product
details are here so we have to do
select from product So in order to run
this query I'm gonna make my orders ski
my active so I'm gonna double click on
IQ If it is highlighted It means it
is May it has made my order schema
active So select What has he done He
has asked So let me just cut place
from here only So I want to display
product class court product I D Product description
Product price strong Brought that So this is
my simple query that I will write first
Now there's telling me something else that create
one more column which is ask for this
If the product class cord is 2050 increased
the price by 2002 051 increased the price
by this and so on So then we
have more than when we have If then
else statement be used The if function when
we have more than two If the nest
if then else if then else it then
what We use case So we're gonna use
the case statement here Okay so I'm gonna
write another column So calm are Please do
not forget to come over here reading another
column case and product class code So whenever
the product class court if it is equal
to 2050 if the product class scored if
it is 2050 I can also write case
if product class sport is equal to 2050
both way will work If it is that
then what I want to return I want
to eggs something right Increase the price by
2000 So I won't Product price Les 2000
If the product class court is 2051 increase
it by what if it is 2052 Well
im sorry you can't use if I'm sorry
it has been I got confused with And
if there is nothing like that s what
we want We want the same product price
So do not forget as statement also And
and also we need end also that is
the simplex Okay I'll give it a nice
name New rice and run it So we
need to sort also because they have asked
us to sort on something Ah soared them
in descending order troughs category Soul order By
that dignity What does get degree Is there
a product category No it's the product class
called only what does hard second goals Nuclear
is a day Yeah Write a query to
display the Florida description for the class description
and product price off all products which are
shipped Okay so we have So you use
these four tables by because we want product
description I think product description is in the
table Products product class description is in the
table for the class Product price isn't fraud
A table ofall products which are shipped and
I think ship business or the right terms
and order Header So let's look at the
diagram So we want product class description product
description product price So these are the three
things that we need but we want only
for orders which are shipped Okay So our
debts ship this from the order so we
have to join this product with the order
header Okay And then from the order header
we will get our order status which is
shipped And uh why we have to join
it with order items Was it anything here
Product price which is shipped They brought the
ridicule to you Product idea Ito product table
mayhem four Oh ha Product market Your product
idea or that idea handling caught They have
the ship that you have all your hands
Okay so that's why we have to join
four tables Okay so let's quickly do that
Celek Let's first joined papers from broad ups
Product Join broader class on P and PC
on Be dark for the class court Is
he called to PC Dark Okay now I'm
going to tell you one more thing Now
the way we know how to join papers
So there is one more method off joining
paper Then we know that Then the column
There is a column of common column there
in the two papers for the class court
and for the class code here there is
one more met occurred which you can use
so people pay attention Look I'm doing it
So instead of writing these two lines I
will just simply write this line natural joint
and get rid off this thing huh When
we say natural joint What a school thinks
natural means the same keys in the stable
and then the same keys in this table
Whatever common key it finds it automatically Joints
huh No no Again one by one That
the way I told you Not like natural
joined this natural joint Uh huh Yes What
even But he's that same The column Name
is same column Name needs to be same
If it's the friend you can use natural
joint who live in the column names our
C Huh Remember when we were doing the
dot manager I physical to eat dot employees
I didn't There we can use it because
the column names are different But when were
you doing either or department I physical toe
DDR department I did there we can use
Okay so let's try this one Okay Unless
let's ah b dawg product I d Oh
my be dot product description coma pc dot
product class description Okay then Natural joint Now
let's see if there is a common key
support the tidy and the product idea in
order items So there is a common key
I can do natural joint Okay So what
was the question The question waas right Equity
to display the product description product class description
and product price What is the product price
There is a product price in the product
table only I think so Let me change
this a little bit So let me dio
or dot or I don't ordo i d
So for this order they have placed How
many products Um 1234 by four items they
have placed in the first order One was
the TV one was in front sleepwear Samsung
Galaxy and four level premium chair And this
was the product price And so what else
were they Only want to see where the
products are shipped Okay so let me show
the ship also What is shipping That is
order status Right And that is in the
hole I table order items stable I think
No our district is in order Header story
Okay so ship ship ship but there must
be few which were not shipped in process
We don't want the products which are in
process Okay so we want to get rid
of those so we just put a filter
there Order status is a call to Yep
Okay So before there were 205 rose only
now 1 68 rows and hotels Oliver bitter
ship bets it So this is my quickie
Okay I'm gonna go tired also Should we
go third and then take a break Okay
So natural joint I told you normally how
will doe Normally we will use the simple
inner joint right So we warned Don't find
out Select Plus name Last name the Bosman
name So because we want the five men
name we want tojoin employee table with the
department table So this is how I start
You do right on e dog department I
d is equal Toby dot department idea Initially
we should start with this This is the
logic Okay A It is possible that sometimes
we do he dot employee i d is
a call to something else dot manager i
d When the column names are different Vendor
column names are same We can use natural
joint But when the column names off joining
are different Then we could not use natural
joint because here the column names are seen
We can replace this These two lines with
this line from and e you can give
in Yes You can't give alias It's up
to you Natural joint department B and you
don't need to give on what Because natural
joint or automatically thinks that there is some
common column between the two papers It automatically
pick department i d from employees department I
d from department Enjoy So like in this
example if I write on on on the
query becomes a little complicated Okay so we
can use lend the also so we can
use natural joint But have you have to
be 100% confident that the name off the
columns in those two tables are Exactly since
only then natural Joinville book How envy All
there should be only even call them That
can't be two columns of two Collins again
It will get confused OK so only one
common column He couldn't do the third one
Then we go for a break then go
Don't use natural joint Go by simple joint
and palette on which column you want to
join Okay The tired one Write a query
to display the customer i d customer name
email and order details And they have given
the order details Sub tota product wanted in
tow product price for all customers even if
they have not ordered any items Okay Huh
So what do you think Even if they
have not ordered any items Okay so customer
places order So online customer and the order
header These are the two called to Tabor's
which has information about what order I have
pleased Okay so then I joined these two
tables Let's say there is a customer who
has not placed an order yet I have
joined flip card today only I have not
placed and the order But the flip card
CEO wants to find out the list off
all off its customers Even if they have
placed in order If their place in order
tell me the order detail if they have
not placed an order Still I want to
see those customers which are there which have
signed it on my website Okay if I
do simple joined here in our joint order
header and all line customer You think you
will get the customer which have not placed
an order No you will not get it
Because in a joint means only when I
have the customer I d here in the
order header because I'm joining It will only
join the that it will give me the
Calman things Customers who have placed an order
Only those one he's saying Give me the
customers who have not placed an order Give
me those customers also So what will we
do huh We will look if we're keeping
the customer table on the left France I
we will place then left outer Okay let's
quickly build this one This looks like a
complicated one but I'm telling you step by
step if you do it it's not that
difficult So let's just first select this customer
idea Customer name E mail Select customer I
d Customer name E mail from Oh line
customer Okay because tomorrow Okay so let's Koncak
customer f name goes to my l name
and call it well name customer email everybody
Okay Palau just customer information I want his
order information So order details I want this
information or the right day Product description Product
quantity Okay let's me show Let me see
the order one Order head Oh order I
d Mama what is product description that we
don't have Okay so let me first join
this on product quantity Also they want so
shouldn't or doing it Okay we have to
join it on that online customer natural joint
or does head Oh right So calling customer
natural joint order Heather it was joined it
on customer i d That order Heather Natural
joined with order items that will join it
on order I d This is customer This
is uh order head Oh and this is
order items Okay I have to give customer
i d c dot advisedly complain and uh
order I d I'll also start and let's
try to run this one So we're getting
some data and then we want from the
order items Rod that i d broader quantity
And I've also want product description Right So
for that I have to again do natural
join with the table product and then I'm
going toe product table and then product description
Hey product ideas in many So let's to
be dark product tidy Let's run this fun
Oh broad So this person has placed an
order This is a product I did This
is a product description and these are the
number off products he has ordered Everybody getting
it right Okay What else So we have
done the customer I d customer name email
or the righty for the description Further quantity
Okay so let's try now Product quantity in
two Product price sub total It's a portal
related Let's just first find out this column
Okay So product quantity into product price And
then this is okay for the country Prior
price And be only and we want it
sub total sub total means some Okay Sapkota
Based on what Let's see Oh for all
customers even if they have not ordered any
items So let's don't group by customer um
for all costs upto okay Oh actually it's
not some Let's not do some Okay so
this is a product quantity and this is
the product price and we're doing a multiplication
or board in order to get okay Because
the person has ordered three in front seats
It has given me to 50 multiplied by
37 $50 for the product quantity into product
price So sub total doesn't mean sub total
It means that we're doing that sub total
by doing product quantity in two product price
Okay And then they're saying even if they
have not ordered any items So you will
see here people only who has placed an
order Let's say there are a few people
205 rose They have not placed in order
yet So what we do we cannot go
Ah the natural joint here So we have
to do here Full not for left out
of joint So before there were 205 rows
Let's see what we get now left Other
than the good customer is in on the
left hand side What Oh we have to
give it Sorry on C dog gusta mud
I d is equal to wedge dog customer
I d So before there were 205 now
so there are no customer here But if
there are other customers let me create quickly
clear Just I want to show it to
you So in suck ah into customer online
customer and ah customer I d comma I'm
just trying to create a dummy customer customer
F name and I think customer gender These
are the three men date three column So
I'm just creating those We'll use 9999 Let's
said you know and then so I have
added one road the middle So when I
now run it there will be 200 Still
that is 200 And by I think once
you put the left outer join let me
just don't you to make anything you let
me just try it I think you have
to put left outer Join on everything on
Well I Oh I may have brought that
I d Oh now I got it still
No Oh I'm displaying full name and customer
email So 9999 is there OK but because
the other one is not let has made
the whole thing Now are you getting what
I'm saying So hold on let me enter
one more things So let's make it 1111
Some need Aurora male and then customer Lastly
Okay And run it 27 rows and some
meat so somebody has not placed an order
But still you are able to see it
Why You were not able to see my
record Anybody Any idea Because I did not
give my last name So when a place
for lame sore enough last mile it has
made everything now So it was showing my
i d 9999 But it was not showing
my name Okay so that's why I couldn't
find my name But 9999 waas the Okay
so let's look at the Grady now So
what we learned from this That if you
are doing Left outer Join you have to
do Left outer Join on all the things
Okay You have to do out Left You
cannot do Left outer join and natural joint
It will remove those extra records So you
have to want to have you left out
of joy Do left outer Join on all
the other tables Store it as a word
fight Insert all the queries after each question
Save this dense ended in a world full
use huh Question can very answer Dad North
bread is also fine dot sq That's also
find We just want to see the queries
You send it in any format It's fine
with us But this was a tough one
you know It was a tough one to
do but if you are able to do
it you know Pat on the back I
think this week probably I'll check with them
They can't be on outer joined It has
to be that left out the joint All
right out control So we get think customer
information We're getting the order information and the
product information and this report will be very
very useful So the flip card manager right
Let's just export it and let's see customer
or dough see of it Okay So you
he will come to know that this person
has placed these products has ordered these products
and this was the amount that he has
purchased So this way he can find it
out Who has placed what How many Okay
And see this this customer the semen She
has not placed an order but still be
ableto see that customer similarly And mud has
not placed an order uh hunts and uh
the does for all the customers who have
not placed an order So what will be
the benefit off Getting the support I'll probably
create a um I'll create a campaign that
whoever has not bought anything from me I
sent them some coupon 5% off 10% off
Okay So that give them incentive to come
to their side and then place an order
And also another uses if one customer is
placing a very order for a very high
end products PV laptop Okay And he's our
our preferred customers So what will You will
send them also some coupons that there is
5% if you buy something more than 20,000
We will give you 10% discount This is
how we get me miss right They are
running these queries analyzing this data and then
taking the business decisions Okay Okay let's take
a break We come back then we'll continue
with that Uh queries Yes I love 11
30 Will you come back by 11 45
So what are the analytical focus student What
do you mean by analytical functions or how
the operator A data who Okay so I
gotta get function We also can apply using
grew by raid So when we apply ah
aggregate function on a group by I mean
after doing group by what would be the
result that will be expecting would be expecting
Let's say if we have Ah this type
off a data Let's see Okay Just a
minute Yeah We have a diss type of
data on what we have in the region
Okay Not southeast waste Not so And then
we have some region number and then we
have a sells for that particular region Okay
let's see if I uh say that Select
our cell Okay From this table Whatever this
table name is group by Egypt Okay so
what will We will be expecting that Yeah
So what way really have it now in
the stable What we have is we have
four regionally So for every region Okay whatever
sells well you have So there might be
three sells for a new 400 there might
be five cells value for our religion There
might be some 6 to 7 sells well
for average So you can't have a common
count for every region OK Only thing What
it will do is if we seek eHarmony
North loser that Okay All those values will
be come to their together I'm all those
data Okay You'll apply some water that I
could get function It might be some It
might be out it right It might become
It may be mean it by remax So
there are a number of aggregate functions I'm
you will be applying that aggregate function on
that data and what it will be a
good thing is you'll be getting your one
result for one group right Doesn't mean difference
between windows function on I agree I'm agreed
Function on grew by so after Camellia Ohara
Group Gilly a guy get function gone Somalia
Yeah So that's what we generally doing the
group by right now in windows function I
visit you again Cannot play aggregate function on
using windows function But difference here is you
don't give one value for one Uh guru
Rather do you get You can get n
values for Ambrose Okay You can get Let's
have keep us You get the in certain
to keep making animal Mr Temple eight records
a death late 90 courts of that So
in those 90 of course If you apply
agree get function Hi Oh being proficient are
doing ordered by whatever the right number of
ways to do it The main thing is
that they don't expect one Well you for
one group Okay It will be nine values
for mine Rose Yeah There is a different
way in calculating about nine Malukus A calculator
The cumulative basic of hungry cumulative A sick
of me Wrong It was a like a
like but you have a counter Have been
best function absence apply can make a time
window apply Carter But then when you apply
in a window you applied Royce Okay not
group ways Yeah You don't know that Basic
differences begins now So Sub Sabella we need
to one me apply on on any data
set when we apply with desperation Inundated that
I said we need to first define a
window We need to first if I remember
What Which will be applying Amy windows from
windows functional analytical function Okay that can be
done using a were close That can be
done within or close So subset paella Pavillon
called making work closely together So let's say
in fact I had this type of a
query Callie culpa Select salesman course Elfman coating
that 010 to 803 Region region is not
south east west Okay on Then sell small
themselves One new model of kidney cells who
are here I'm Jack Anapa After average car
sales while you're applying aggregate function on the
cell phone What Yeah it's no sector So
you are writing that what I mean at
or you're not writing anything Okay Africa Italy
come here Okay That means right now you're
not being a meager and with a partition
on your data okay Mrs Hayward Lika assured
there Can you see this investible or in
black it Nothing So generally what you need
to specify here is you need to specify
what kind of partition you are making on
within that partition How are you want What
kind of ordering One you don't use upto
Bethany Bharatiya When you apply windows function can't
remember three things One is or what So
what It means concentrate that you put up
like Okay so that they passed it Does
it okay we didn't know Are you need
to specify Actually three things OK but will
not talk about 13 right now Let so
first you be comfortable with irritation and order
by And then you will be looking at
the frame Okay Or in bracket We specify
petition on order by the partition Better pay
Jodi tested company over Campbell but I stopped
by Titian Keurig Abi vision by some uh
three But tradition myself attributes Ok then we'll
see my vision kill But at the time
I got to get function Apply Correa Yeah
So your technique right now we are not
writing anything here That means technically we're not
doing any partition Technically wonder being any partition
Okay So that particular call and will be
calling every terrorist cells from cells OK so
it's calm Oclock Kyowa is coming when you
are Captain Sport you Percoco average calculator You
are not grouping it I'm not by listening
it You're not at ending it Yeah everyone
So whatever is your average value will be
retained for every room That's a difference between
your normal aggregate function Using grew by on
this matter right this method So now how
many average Well listen begetting nine Okay Same
value obviously because sometimes I say me I
but you'll be getting nine Well it's clear
Yes I'm like man no next novel introduced
by addition by close here Okay so partition
by now What I want to do a
same same query What I'm doing here in
black it by Bishan by region Yeah yeah
Under Kelly Coming Yes Cuba By addition Can't
Memory region Cooper So 95 partition This region
caller You have four regions right So the
similar reasons will be considered together will be
arranged together we will return together So you
see here East east has come together north
not South s so and then this will
be generally arranged alphabetically Okay Region seven alphabetically
arranged Younger Now within this region how I
want to order that are not specified Okay
right now Okay So next sex one will
be cock up Mama Same couple but I'll
be partitioned by regional Vicky But I want
to perform average on self swollen right so
average will be performed But it will be
only for one particular region Yeah to you
25,000 and 24,000 coverage huh Will be returning
in front Off both the records Yeah River
Similarly for all north region you'll be careful
Looting average So average of mystery values will
be returned here in front of three rows
Okay fine Yes OK Next but order by
Diego Dichter Ronan But hey so that it
picker Okay now that is one just small
addition I want Oh Januvia Let's say five
right Like this way Okay Cyrix Salesman Court
Kama region Cover cells Won't you come average
yourself Swallowed by fish In my reason Whose
craggy fire right order by let's say sell
swallow order by cells one you as ourselves
from cells What kind of a difference you'll
be expecting or what color for a different
result will be getting our database for after
you do Yeah but not met in Vienna
It will be ordered by cells for you
Okay if you have a different side So
small Little by little Come first Oscar but
higher value bet 20,000 50,060 Sanders Curry Now
he got 40 45,000 Rabia right You Berruga
was about 50,000 escape on one leg and
ask about for like a year s What
would be the difference in this result Nor
difference are that we've been a difference So
you don't run this query numerically Go far
this squid even our daughter by close Okay
so this was a very simple one The
partition by right partition by No what I
want to add its I would black All
right everybody did Okay Seven I think Partition
light Is that a difference Yes So earlier
you had same value for both The road
map in Bogota in doughnut average scattered $29,500
Capita You know we have an attacker that
miss if you happening were ordered myself Volume
Okay so ordered by cells form So obviously
this will get arranged Okay on now Well
how is this How did this 25,000 So
when you write older Mina it is considered
a single you know at the time OK
for calculating average we're concluding out It's now
It's not an entire partition or which you
are applying I gonna get it's by Titian
by I'm inside The border bites already also
should be calculated including order by clause Okay
Are you getting so first row Then you
have unpacked nation within that one partition You're
saying order by So first value is 25,000
So if we consider only 25,000 was average
25,000 now within that partition out of a
model So that is 34,000 Was that having
to go back 29,005 Okay so that is
there for that particular partition for a new
probation again for a new partition again Fresh
new So 20,000 Right So within that vibration
So this is your vibration So within this
partition 20,000 is your first raw was average
40,000 if you are one motor Lewin boots
so don't know average 30 34,000 like a
cumulative average Uh give me to do I
would get so that was it 10 60,000
67,000 if you in this partition was average
45 Yeah you're different Do not you not
get same results Knife Yes on my map
Okay So South Delia one lack for less
So Mocha Joe be average in the best
chili again 45,000 1st time then 50,000 Right
Okay Now then see next the simplest I'm
a bagel function Okay substance Simplest at all
number Okay So number Caterpillar roll number We
just assign their own number Two year number
of records Okay Now again when you are
sending wrong number you can't If I know
you have to define our class If you
don't write anything in the work loss it's
three Consider your entire day Pass it on
We last time situation or numbers to it
Okay I got up a petition by Mullah
broke Azarenka partitioned by partition It will assigned
or number okay in order to buy But
there are in America I don't get a
gun and then it will s a little
number Okay I'll show I executed Then we'll
understand it better Okay okay Bad thanking me
up This is This is saying this is
same salesman core region cell cells follow Missy
Now what I want to do is I
want to assign our own number Okay Callie
Coming now what Five Patient by salesman Cornyn
Order by cellphone Sim Okay As he real
number So I'm assigning a serial number to
each of you Me cereal Lamberto e true
That's a new column name I'm giving Okay
Jobe resulted with Jimmy Leggett 90 Like my
column with me Like I was calm Pulling
the serial number is the name of that
column And then average he actually is a
rightly Yeah Okay so what I have done
this Okay if you can interpret this result
Yeah but like you don't know about the
same corner me a partition concert Partition Cooper
I want to partition this daytime by region
ways Okay so in reason salesmen court a
region salesman cortical but a big part of
the same self Mancora tapioca that will be
considered together within this partition Water How I
want orange I want to arrange by cells
One so self for long will be arranged
Ascending order Okay Now you see Petition may
I'm gathering it How about one about the
same getting Soro number everyone into But don't
nobody just like sequential number Okay so one
into the of our vision Catamarca makes partition
Caliphate Zero number will start from one Okay
Is because the spy business as took up
my vision started has three competition There are
three rows So do you go from 1
to 3 And then as for Kelly again
Oneto So if I don't write partition number
then Karaca Partition diagram Acrylic Titicaca I'm gonna
tell you partly Khaldiyah So your cellphone first
will be arranged in ascending order on then
1234 in serial order Good It so that
you get experts See it Yes Now that
I'm cynical Lanky You know I want to
know the second highest cell phone told her
yourself or new gets me going It is
May Can you find out second by yourselves
Formula Our lettuce uh least sells least sense
on your Okay So let's hug Anomalisa are
better by descending Yah What I'm going to
get this Hans upset How Yourself or Loma
It will be on top Oh second biases
your one Look I want to face that
That is that the court Should I get
it It's a Kuwaiti court found on the
second by your salary Niccolo you know I've
been missing invasive about We don't know much
Say that That's out there Now that's the
real numbers of people right Yeah Ben trying
relive that Their study Beltran Bill is a
Parang bill is not an issue but nobody
will give in unique uh number doing judo
Okay everything So when you write dance rank
is accustomed to topical Pepto does the asset
team Rosie s antenna Okay you will be
like a cartography sells only Munich Let's say
if you have a unique sells for new
then all well one limbs will get a
unique number Leandro number Okay but that Okay
so if it's hurting him make sure that
I didn't see you Cigarette number No Sure
directed you ago The you need to first
get your result image in red canna It's
quite the big duplicate normalized in a product
apple Okay From daddy How to fish Assad
is a close to a star Serial number
is negotiable Thank a square food up They
will consider car Okay okay From self Stockham
bracket Another thing this couple day as B
Yeah we'll be dot Sandown any scag which
to select at my home 50 Children Okay
so that's how row number we were You
understood in a partitioned by capturing of up
in order by Katarina if I removed or
by if any more that by let's say
I just include partition by what it will
do So are there by Nick Aldea said
Partition Barreca Oh how would you let inch
eyes they're faced That's about your database Okay
Database media's gonna save off a chunk So
you're just operating on our tradition not of
its specific order Okay George doesn't Today that
best man will see ordered by number assigned
area here lag and lead Okay Can do
you know what is lack value A little
value Immune dysfunction Okay lagging guy in general
meeting Care lagging I'm meeting lagging Miss Behind
behind German lagging behind Leading me after a
well again Okay lag windows function will return
of Alucard row before current rose Right now
Yes If you're working in one partition We
did that One partition Let's say if you're
working in a second room Okay Lack capture
accounts kept by live A little cover you
assign carried a second broken because for hasta
it will be for frustrated will be no
Are you getting Let's see if you have
a partition Within that pack nation you have
three values but 123 or three rows 123
No lag Lagging rule for first row will
not be nothing will be nothing right You
don't have a liking for first row Don't
schedule And you're right Women are but second
roll It will be first but third road
will be second Yeah again lag keep null
And I have obsessive high concept drama club
Immediate last local Emma are you I want
to have a like a 23 frames at
the time considered as a lagging friends are
liking Rose Okay that you can specify Keep
Merrill quite a time considered liking grow school
to previous to previous one drove incident on
my previous to consider command prehistory restaurants American
my like that Mr that frame you can
be fake So let's say this simply simplest
off example Let me get good man select
cells Self man core Obama lackeys Cooper Lego
idea like a missiles William Cooper Like I
get to sell Small number can sell for
limb column me previous self value for this
particular law We believe them here Okay Tonight
what you're doing is ordered by region That
doesn't make correcting me Now hum Kagera Hey
Salesman Cork in Yes 03 came here What
we're doing We want lag off cell phone
so far stroking it will be nothing for
stroking Putin era ups Keep teacher You don't
have any specific value Okay Now for second
row it is a foster So it's got
you'll be self small New moga that will
be returned yet Their car broke in here
so I can broken yourself for Newman Oh
yeah Baga They don't myself with a taste
for the telegram We got justice Okay Okay
What was your date Abyss Databases I see
it Okay so what is our condition Our
condition is salesman quarters as the rotary fate
So for all cells 03 Salesman one second
Say they call you a hero This is
your one draw Is the rotary having 25,000
cells Okay If it one leg on then
I 03 67,000 now in the three rules
I'm blames and my specifying in order Yes
Inspecting in order rate or data by region
Eric I'm with you about the war to
even though Let me print regional So Yeah
Okay so he's in evasion We're ordering right
alphabetically Policed value will be first the north
and then south Right If you look at
alphabetical now used self or let me put
it Get it down Yeah Okay Look yet
everyone Now you'll understand properly You have our
original self for long Parliament Yeah my original
self lacking like about there That one It's
column lag on cells column myself or let
go Okay So ordered by picking Now when
you lack that means this is your previous
William some service Kanaan Vera Lag column co
previous cells Okay from Yavapai Ricky Cell swim
ca previous record Previous You sell sta Well
25,000 Forecast later Previous lows Rosie Okay Now
for this law What is your liking Grow
previous Rhona Scott yourself or limit We are
bigger Something man His luck What We believe
the lack of changed completely king Yet the
next drop right you Yeah fine Some of
my how lag and lead walks here Can
we go by Beijing by then We do
partition by the chapter on leave Jenny Khaldiyah
salesmen Ford made a mammy specific which Okay
what I want to do is I want
to by fish in my region order myself
for you Okay By Titian by region order
by cells won't know on within that partition
I want to apply life And you know
that this can you suggest me the query
forya by vision Bay Region So a ticket
Uh lagging Lange's Okay Thank you You expected
them sickening within this Within this part they
said I want blood inch How I want
How are you going I want to arrange
by cell phone Okay on then lead So
previous cells will be liked by one drone
This will be like one Barometer is cover
Luna Lega on discover Literally $5000 is Can
Malaga Esca 20,000 I guys cut 50,000 acre
because in this partition I'm doing lucky within
the partition I'm doing leg right Everyone see
you had their economic shop We know the
way Everything happens over the partition Okay within
that partition you'll be applying Loy's any function
Okay By Titian Catamarca Let's go back to
the start of Apple You need to restart
for exploitation Yeah can This is the you're
Laghi ticket way here Joint Nicaea Need that
lead maybe Cannot be just okay Can you
tell me what This first value Australia But
apple first I Lou come on You re
meeting Gave us first Well you right So
first value in that partition we Britain across
all I mean all that rolls off partitions
right Let's have every man silica so lacking
You got me for a date As let's
say previous cells getting up on Monday Initial
cells Thank you Me first Well so it
will be okay Initial cells But laugh within
this partition was the first value If you
order a plow order by was the first
for literally fertile That is their initial said
that initial Selvi Brittany Everywhere Okay okay fine
Right There is something called the enter Well
what will be the end of alluded So
you can specify and number concept value of
port au force A secondary 34 to victory
and value can specify you Wait one specific
example A minibus No So let's see No
I don't do by patient by Okay Partition
Basic album within that partition and value another
muscular had about you can get by disinterest
abuse adaptable afford value in that partition And
if you just have a partition on tools
you can get for telling that partition So
I'm just removing partition my clothes that Okay
so I'm just writing in 12 years okay
And my new on the cells will you
I need to specify now Number inter value
in movie number specified I'm not gonna get
into cover Look I up Okay Let's see
if I said three Okay but what will
be a good thing is I'm the partition
by her five year you partition But idea
now yourself So Loomis that is Dubai I
am engine ascending order now in in this
order What You're entitled the heart of a
loose 34,000 So you have a copy paste
over So from here that before 1,034,000 everywhere
will be top investor But for us to
rose between it will not be anything It
will be now Okay so and minus one
raw skill You It will be no and
Rosie Okay That really will be called the
Western clear or just same for all those
Yeah Okay Can you apelike by addition by
here Does that not an issue Kurtz Act
a little grumpy partition by Let's um Lily
Kay But he didn't go there Lesion Are
that my sense Once again Okay Tom Interval
literal conquer They're taking care of the right
subject up in the leg Nickel Now within
that tradition within that partition Second value So
25,000 Gag question The A great women Okay
what is the second of a loose second
row 34,000 was actually end of aluminum Basically
went through a stage A local fair calculation
It's not only anti value it's X when
you first and minus waas Those court will
not do anything And um I don't say
he it We uh I mean frame will
start from and throw itself Okay the frame
will start from and through And Tropea was
the value that will be taken Yeah You
obey sells for loom Apga only Adiga as
a gentleman Yeah a sentiment Understood Okay if
we start operating off from entrances Yeah Yes
I'm going man Yeah Yeah And then there
is something There is 11 important function entitled
and I Have you heard about that Any
pain Okay And that he got that ago
Look about that official Some data And yet
what famous question It was the difference between
Thailand and 12 because students generally get don't
know their relation The arrested Oh but shooters
get confused because a similar name Okay foundation
everyone Yeah they said just a big hurry
back here Some good Cut them with a
unified nation back idea Okay I'm not looking
at the data by doing partition Justman order
America yourself So Louis Beverage in ascending order
in that data sick What I want to
do is in this in that particular table
What I want to do is I want
to form I want to form a and
I mean entice and dies Miller and the
groups okay Or any groups Can we have
a directory But you get the number 15
group Anami In short would you get a
man being gruban under Domitien Ruben on a
basket Rosa nine bro that can we do
it equally equally Can sit in a thought
Gaga have three low score Good number one
Bulla Jagger second group Co op group number
20 J Got our group a big group
number three or day hits a magma 1112333
Yeah and i'll not let me Just makes
a three group or any groups in your
people Okay Just happen Able embassy operable order
by order by secreting a descending order beloved
descending order my karenga pick a dietitian bible
at the partition Gulkegroup Karenga Okay so my
vision my gear tradition back here within the
pipe is in three groups Okay of course
Another matter My cab Or do when they
have a position He boulais partition the people
ice coverage Meaning you can write a bun
is one partition because well that they will
take partitioning What's more it'll remember you get
new group when I met with being group
but I mean nine grows out there Nine
Rosa they're equally cuts a potato cannon Justin
Timberlake's quick group money Okay I've got many
happen parabola If I say for obviously one
has to be order group Give me They
can make it Medaglia So If I want
to make her room between with three then
I'm always first Will be having more than
more of those than second Yeah Barack Obama's
first group Geragos Without that ordering second Me
coming Okay so I'll just exit through this
Then you learn this stuff See Faster But
it three rose 111 later on two days
Eat They don't rose it Okay maybe five
books Yes Five When I said this is
what I said big Yeah so did not
maximum equally One about make a liquor medical
in here A lot of one grow in
the Taliban groups and was making grew at
a critical Yeah Okay so in Thailand trigger
partition by later convicted Okay starting please Can
you interpret this result on paganism Can you
In the break when Ebola and by three
But I'm specifying no petition They So that
means visiting the fact nation I want to
make three groups Okay I want to make
three groups but then there are only two
values Nine years for three groups I'm gonna
people's The middle was in here Basically it
was a better resume here No one even
up again It will be just one grow
on the group I could be that one
has up happened for the eastern south and
West East region Killian was no hero Aberdeen
Group Ana may be possible So group number
one is one broke Rule number two is
second That's it Okay Now for not reason
you have three plus three groups So 123
Yeah get everyone Come on man And then
you have last two then Frank and drank
Then today I can drank So what What
is dancing He happy about Bakio gonna dance
rank and drank the board Work Yoga Countered
instinct Yeah So Latif right Happy if I
write like this at a bank Yeah that's
it Ranked number I'm are ending in in
descending order So high Yes two will get
first rank Okay let's go back a second
year September And so wherever you have a
farm in value whatever you have a common
value You get the same same rank same
rank Okay four and four But then five
will be skip in that case because I'm
writing rank Okay If I will be Skip
next Frank will be 6789 Okay And if
you applied then surrounding the Five Lord biscuit
So next rank will be fight Okay to
that The only difference are you subset simply
2nd 123 now foreign four Okay so I
50,000 is four and 45 Lord whisk it
So if I were with an ex transfer
for 35,000 Okay What we know must get
me or guys with Yeah so I'll give
you some questions Can you practice on this
analytical functions Well just uploaded on Olympus So
much mass questions All good of it Ah
break up Take a break Take a break
for 10 minutes I'll just upload all those
documents This brings us to the end of
this sequel tutorial Now before you guys sign
off I'd like to inform that we have
launched a completely free platform called Agreed Learning
Academy We have access to free courses such
as the iCloud and digital marketing So thank
you very much for turning recession Have a
great learning
