DBMS-Aggregate Methods (GroupBy & Having Class)

What will be the output of the following query?

SELECT department_id, SUM(salary)

FROM employees

GROUP BY department_id;

• The sum of all salaries in the table.

• The sum of salaries for each employee.

• The sum of salaries grouped by department_id.

• An error because of missing WHERE clause.

Which of the following is true about the GROUP BY clause?

• It can be used with aggregate functions.

• It must be used with a HAVING clause.

• It is always used with a WHERE clause.

• It cannot be used with ORDER BY clause.

Which statement will cause an error?

• SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

• SELECT department_id, salary FROM employees GROUP BY department_id;

• SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

• SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;

What is the purpose of the HAVING clause in a GROUP BY query?

• To filter rows before grouping.

• To filter groups after aggregation.

• To sort the results of the grouping.

• To combine results of two queries.

Which of the following queries is valid?

• SELECT job_id, SUM(salary) FROM employees GROUP BY job_id, department_id;

• SELECT job_id, department_id, SUM(salary) FROM employees GROUP BY job_id;

• SELECT job_id, department_id, SUM(salary) FROM employees GROUP BY job_id, department_id;

• SELECT job_id, department_id, SUM(salary) FROM employees;

What will be the result of this query?

SELECT manager_id, COUNT(*)

FROM employees

WHERE department_id = 50

GROUP BY manager_id

HAVING COUNT(*) > 2;

• It counts the number of employees for each manager in department 50 where the count is greater than 2.

• It groups by department_id and filters the count greater than 2.

• It groups by manager_id and department_id and counts employees where count > 2.

• It filters employees with count greater than 2 before grouping by manager_id.

Given the query below, what does it do?

SELECT job_id, AVG(salary)

FROM employees

GROUP BY job_id

ORDER BY AVG(salary) DESC;

• Groups employees by job_id and shows average salary.

• Groups employees by salary and shows average job_id.

• Orders the employees by salary in descending order before grouping.

• Groups employees by job_id and shows average salary in ascending order.

Which clause is used to restrict the groups of returned rows to those groups for which the specified condition is true?

• WHERE

• HAVING

• GROUP BY

• ORDER BY

What will happen if a non-aggregated column is selected without being in the GROUP BY clause?

• The query will execute and return correct results.

• The query will execute but return incorrect results.

• The query will return an error.

• The non-aggregated column will be ignored.

Which of the following queries is equivalent to the given query?

SELECT department_id, COUNT(*)

FROM employees

GROUP BY department_id

HAVING COUNT(*) > 10;

• SELECT department_id, COUNT(*) FROM employees WHERE COUNT(*) > 10 GROUP BY department_id;

• SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 10;

• SELECT department_id FROM employees GROUP BY department_id WHERE COUNT(*) > 10;

• SELECT COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 10;