SQL Query Practice Question

Que 1:-Write a query to find the number of employee in each department. Given table is following.

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

                                                                 (tbl  )

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

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 ?

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.

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;

This covers our post regarding the few sql query practice questions, if you have any question or if you want to add anything then please comment below