Que 1:-Write a query to find the number of employee in each department. Given table is following.
(tbl )
Ans: Before reaching to the exact answer of the above question we will see some basic concepts .
Now the question is what will happen if we write the following query
select * from tbl group by department;
result is:-
| Emp_id | Emp_name | Department |
|---|---|---|
| 1 | ishant | cse |
| 2 | rahul | ece |
| 3 | ankit | cse |
| 4 | deepak | it |
| 5 | anshul | it |
| 6 | anurag | ece |
| 7 | aman | cce |
| 8 | rohit | it |
| 9 | rajesh | cse |
| 10 | Kamal | ece |
Ans: Before reaching to the exact answer of the above question we will see some basic concepts .
Now the question is what will happen if we write the following query
select * from tbl group by department;
result is:-
| Emp_id | Emp_name | Department |
|---|---|---|
| 9 | rajesh | cse |
| 10 | kamal | ece |
| 8 | rohit | it |
I am sure you must be wondering why it is just giving the single row rather you have applied group by on department .So the answer is of your question is in the folling question.
Suppose I have a table Tab1 with attributes - a1, a2, ... etc. None of the attributes are unique.
What will be the nature of the following query? Will it return a single row always?
SELECT a1, a2, sum(a3) FROM Tab1 GROUP BY a1, a2
Answer:-
GROUP BY returns a single row for each unique combination of the GROUP BY fields. So in our example, every distinct combination of (a1, a2) occurring in rows of Tab1 results in a row in the query representing the group of rows with the given combination of group by field values. Aggregate functions like sum() are computed over the members of each group.
Now coming to our original question(Write a query to find the number of employee in each department.).
We know that we will have to use count() aggregate function to count the number of employee in each department after applying group by on department we will get the required answer.
select count(emp_name) as no_of-employee, department from tbl group by department;
| no_of_employee | Department |
|---|---|
| 4 | cse |
| 3 | ece |
| 3 | it |
Write a sql query to print the name of employee in each department ?
Ans:
select * from tbl group by department,emp_name;
| Emp_id | Emp_name | Department |
|---|---|---|
| 7 | aman | cse |
| 3 | ankit | cse |
| 1 | ishant | cse |
| 9 | rajesh | cse |
| 6 | anurag | ece |
| 10 | kamal | ece |
| 2 | rahul | cce |
| 5 | anshul | it |
| 4 | deepak | it |
| 10 | rohit | it |
How does the above query work ?
In above query group by return a single row of every unique combination of department and emp_name. emp_name has been sorted in alphabetical order.
In above query group by return a single row of every unique combination of department and emp_name. emp_name has been sorted in alphabetical order.
Que 2:- Suppose if the same question( Write a query to find the number of employee in each department.) but we have two different table of employee and department as following . Then how does the query goes for it.
select count(*),dept_name from tbl,tb2
where tbl.dept_id=tb2.dept_id group by tb2.dept_name;
where tbl.dept_id=tb2.dept_id group by tb2.dept_name;
No comments:
Post a Comment