More clauses in SQL

-

Hello, and welcome to the blog. In this blog we are going to talk about "More clauses in SQL".

We learn about "WHERE" clause in this blog. Now its time to learn more clause which are going to help us to be more specific about retrieving data from Database and eventually its gonna make us more and more efficient in handling data in Database.

So, In this blog we are going to talk about

  1. GROUP BY clause
  2. HAVING clause
  3. ORDER BY clause
Now, let's talk about

1) GROUP BY clause

  1. This clause is often used with Aggregate Functions (which we have learnt in previous blog) so that we can group our result with a particular column.
  2. I know it sound little confusing but we will solve this confusion with the help of example.
  3. Let's consider a scenario where I told you to count number of people working in each department with our existing Employee table.
  4. So then you might write a query like this:
  5. select count(*) from Employee where Department='IT'
  6. Then you will keep replacing Departments in the query and tell me the result. In this way you completed your task but you wrote 4 queries for that as we have 4 departments.
  7. Now, what if I tell you we can do the same with GROUP BY clause and in a single query.
  8. Here's how : select count(*) from Employee GROUP BY Department
  9. I got the result but I don't know which count belongs to which department so, let's change our query a bit : select count(*), Department from Employee GROUP BY Department

2) HAVING clause

  1. HAVING clause is used when you want to add some condition with Aggregate functions. You might argue now that we use "WHERE" clause for that.
  2. So, I want to tell you that "WHERE" clause doesn't work with "Aggregate Function". Yes you read it right.
  3. Consider a scenario where you want to find all Departments where number of Employees =2
  4. Let's see a example : select count(*), Department from Employee where count(Department)=2
  5.  Error Message says : "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."
  6. Now, let's try same thing with having clause:
  7. select Count(*) as Count, Department from Employee group by Department having count(FirstName)=2

3) ORDER BY clause

  1. This clause is most simplest one and probably most loved one because you don't really have to think before using it.
  2. The ORDER BY clause is used to show your desired output in ascending or descending order of a particular column.
  3. Yes, you can also manage order in which your record should be displayed.
  4. 'asc' is used for showing data in ascending order and 'desc' used for showing data in descending order
  5. If you don't specify any order 'asc' or 'desc' then by default ascending order is applied.
  6. Now, let's have some fun in doing examples:
  7. Consider a scenario where you want to show data in ascending order of First Name
  8. select * from Employee order by FirstName
  9. As you can see a haven't use 'asc' or 'desc' after my column name and I got list sorted in ascending order.
  10. Now let's see some records in descending order of their salary.
  11. select * from Employee order by Salary desc
  12. Now can we use order by with 2 columns? Yes of course, let's see a example
  13. select * from Employee order by Salary desc, FirstName asc
  14. Now I tell you what has happen when we used order by with 2 columns first it sorts the records with "SALARY" descending then it checks records with same salary and sort them in "FirstName" ascending.
Congratulations on learning some more clauses in select statement. See you  in some another blog till then "Keep coding, keep learning because trust me you can make it big".

"Have a great day"

Comments

Post a Comment

Popular posts from this blog

SQL Joins | Inner Join | Left Join | Right Join | Full Join | Self Join

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about " Joins ". In the  previous blog  we have seen most important topic of Primary Key, Foreign Key and now its time to join some of the tables. So, In this blog we are going to talk about What is join and its purpose Type Of Joins Inner Join Left Join Right Join Full Join Self Join Let's  start with 1) What is join and its purpose There is a clause in SQL called Join. And its purpose is to combine rows from two or more tables which are connected with each other by a common column. When I say connected with each other by a common column I am talking about Foreign Key which we have covered in previous blog. Although if you haven't enforced foreign key for this blog but we do have a common column then also you can perform jo...
Read more

Update, Delete and Truncate Commands

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about "Updating and deleting data". We learn about "Inserting and Retrieving Data" in  this blog . Now its time to learn updating and deleting our mistakes. Yes with the help of the UPDATE and DELETE  statements  you can actually update and delete wrong entries in your database respectively. So in this blog we are going too talk about  UPDATE statement DELETE  statement TRUNCATE command Let's start with 1) UPDATE statement : UPDATE statement is used to update a specific record or a bunch of records which exists in your database table. If you made a mistake while inserting data in your database then UPDATE statement is the loophole through which you can correct it. Let's see a example: If you can see in our table I have made a mistake that SALARY  of "Sales Per...
Read more