When we want to extract records on the base suffer conditions we would use the very close bite if you want to extract records on the basis of a condition Bardack condition is with respect to an aggregate function then you can’t use the very close And this is where you would have to use the having close So let’s understand this Command respectively having close over here Now let’s say we have this entire table over here now from this entire table I warn the average salary where the average salary is greater than $10,000 and this averaged at least with respect department.
I warned this on leave that the count of department idea is greater than or equal to tow This means that there are east to employees who are more than two employees in that department So in that case we see that only department I D Number well threats where the department ideas 12 Only This is the case that we have at least two employees in the department So we have this employee i D which is common entry and employee I D which is 100 Now we have this department and from this department will have to find the average salary So we have the hotel sadly to be 30,000.
When you do 30,000 by two you get another a salary of $15,000 So this is how you can use the having clause and put up a condition with respect to the aggregate methods Now again let me show you my table over here so I type in select start from on Then I’ll give in the name off the table which is employed over here So select star from employees now Similarly let’s say I want to find out the average salary you with respect to only those departments that there are at least two or more than two employees.
Where I Lewis I type in Select a ridge off salary and I also want to have a glance the department to select average off salary department from employees And I have to group this with respect or department So I use group by department after this values that have in close and I give the conditions so having And as you see I’m giving the condition where the conditioners based on an aggregate functions are having come off department as greater than are equal to two So let’s have a look at the stable.
We see that there are only two departments So the Foes department that’s content where there are two employees and then the next department at stake whether at three employees So let me on this And as you see the average salary with respect to content a spirit and $50,000 averaged Salvi with respect Attackers $433,000 and the God presented respectively these two departments because there are at least two employees and only these departments Now let’s go ahead and work with the order by clause So order by clause is used to arrange our table in either ascending order or descending order and by default Viva Never reuse the order By close we sorted in ascending order.
If we have to start it in reverse order then we’d have to use D E s over here So that’s it We have this particular table of what you are and if we want to are in this table in ascending order But after type and select staff from employees After that I used the order by keyword So I want to order this people with respect to the salary column And if I want toward this in descending order I use that keyword D e E S C Now that’s true Those similar thing over here So let me first have a glance the original table So I’ll type and select Start from MM floor ease and let me hit Run So this is our table.
Let’s say I want to our inch this people with respect with descending order off salary So I type in Select star from employees and I’ll type in order by I have to give in the column by my side one warder So I want to order the stable with respect to the salary Call him and I work toward this in descending order So I’ll just add the SC now Let me hit execute And as we see we have ordered the stable in descending order So the first record to see as the maximum salary and the last record which you see has done minimum salary.
We’ll walk with some psychopathic goes in secret The first set operator we shall be walking with us the sequel union So this is used to combine the results like after World War Select statements and the final results that would have resulted without any duplicates So what is this game means this Let’s say if you have to select statements and with the help off to select statements into with the help of the first select statement.
We’ll get three records and with the help of the second select statement will get four records Now What we’ll do is we’ll find out the results said off the first records and the 2nd 4 records from these to select statements and if they have any duplicate records they’ll be removed And what happens with this is eat Stella Select statement from the union must have the same number off columns So let’s if with the first select statement you are using three columns then it doesn’t street at the second Select statement should also have three columns It cannot be less than three or more than it should be Exactly three columns and also you must meet your type whatever data types off the columns you are using and both the select statements they must be seem.
Let’s say from the four select statement The first column is off in either type and the second column is off back our type Then you must make sure that it is the same in the second sentence statement as well That isn’t the second static statement The first column needs to be off in danger Type on this second Gotham needs to be off that guy like so the test You can also use more than two Grady’s and you can club them using more than one union statement So this all of those 30 years too much complicated for you Let’s understand that the head caught this example So here we have two tables.
The name of Before stabilised Product one and the name of the second table It’s producto and in these two people’s we have the same columns Incorrect one we have can’t agree idea and product name similarly and cannot do We have category I D and product name now or Fred doing us with the first select statement We’re here We are selecting the product name column from this product one table Similarly from this select statement that pop this select statement we are selecting this rap name calling from the product to table.
Excitement would rate on us Nokia Samsung being Nikon and similarly this select statement were Britain All of these values from this stable Now when you use the union operator in between these two this will give us the result set off boot off the select statements So that would mean whatever common that use out there Who was the beautiful So from this will get Nokia Samsung HP a Nikon and from this water Merrick star there So L z Dell Apple and Police Station So these are the ones that are not present in product one Right So basically even the use that union operator really get the isn’t set off book of the select statements So let’s go to my secret on and implement the union operator So my first asked over here would be to create those two papers so I can create a bill in the name of the full stimulus productive one And inside this.