Group By Clause:
Group By clause is used for grouping the records of the database table(s).To use group by clause we have to use at least one aggregate function in Select statement. We can use group by clause without where clause.
Syntax for Group By Clause:
SELECT Col1, Col2, Aggreate_function
FROM Table_Name
WHERE Condition
GROUP BY Col1, Col2
Interview Questions on Group By Clause :
1. Write a Query to get the department and department wise total sum salary from employee table.
Ans: Select department, sum(salary) as TotalSalary from employee group by department.
2. Write a Query to get the department and department wise total sum salary, display it in ascending order according to salary from employee table.
Ans: Select department, sum(salary) as TotalSalary from employee group by department order by sum(salary) asc.
3. Write a Query to get the department and department wise total sum salary, display it in descending order according to salary from employee table.
Ans: Select department, sum(salary) as TotalSalary from employee group by department order by sum(salary) desc.
4. Write a Query to get the department , total no of departments, total sum salary with respect to department to from employee table.
Ans: Select department, count(*) as departmentCounts , sum(salary) as TotalSalary from employee group by department .
5. Write a Query to get the department wise average salary, display it in ascending order according to salary from employee table.
Ans: Select department, avg(salary) as TotalSalary from employee group by department order by avg(salary) asc.
6. Write a Query to get the department wise average salary, display it in descending order according to salary from employee table.
Ans: Select department, avg(salary) as TotalSalary from employee group by department order by avg(salary) desc.
7. Write a Query to get the department wise maximum salary, display it in ascending order according to salary from employee table.
Ans: Select department, max(salary) as TotalSalary from employee group by department order by max(salary) asc.
8. Write a Query to get the department wise minimum salary, display it in ascending order according to salary from employee table.
Ans: Select department, min(salary) as TotalSalary from employee group by department order by min(salary) asc.
Group By clause is used for grouping the records of the database table(s).To use group by clause we have to use at least one aggregate function in Select statement. We can use group by clause without where clause.
Syntax for Group By Clause:
SELECT Col1, Col2, Aggreate_function
FROM Table_Name
WHERE Condition
GROUP BY Col1, Col2
Interview Questions on Group By Clause :
1. Write a Query to get the department and department wise total sum salary from employee table.
Ans: Select department, sum(salary) as TotalSalary from employee group by department.
2. Write a Query to get the department and department wise total sum salary, display it in ascending order according to salary from employee table.
Ans: Select department, sum(salary) as TotalSalary from employee group by department order by sum(salary) asc.
3. Write a Query to get the department and department wise total sum salary, display it in descending order according to salary from employee table.
Ans: Select department, sum(salary) as TotalSalary from employee group by department order by sum(salary) desc.
4. Write a Query to get the department , total no of departments, total sum salary with respect to department to from employee table.
Ans: Select department, count(*) as departmentCounts , sum(salary) as TotalSalary from employee group by department .
5. Write a Query to get the department wise average salary, display it in ascending order according to salary from employee table.
Ans: Select department, avg(salary) as TotalSalary from employee group by department order by avg(salary) asc.
6. Write a Query to get the department wise average salary, display it in descending order according to salary from employee table.
Ans: Select department, avg(salary) as TotalSalary from employee group by department order by avg(salary) desc.
7. Write a Query to get the department wise maximum salary, display it in ascending order according to salary from employee table.
Ans: Select department, max(salary) as TotalSalary from employee group by department order by max(salary) asc.
8. Write a Query to get the department wise minimum salary, display it in ascending order according to salary from employee table.
Ans: Select department, min(salary) as TotalSalary from employee group by department order by min(salary) asc.
No comments:
Post a Comment