A table Employees(EmpID, DeptID, Salary, HireDate) contains 10,000 rows. Indexes exist on DeptID (clustered) and Salary (non‑clustered). The query is:

SELECT DeptID, AVG(Salary)
FROM Employees
WHERE HireDate '2020-01-01'
GROUP BY DeptID
HAVING AVG(Salary) 50000
ORDER BY DeptID;

Assuming 30 % of the rows satisfy the HireDate condition and there are 5 departments with roughly equal numbers of rows, estimate which index(es) the optimiser is likely to use and state how many rows will be examined during execution.

Question

A table Employees(EmpID, DeptID, Salary, HireDate) contains 10,000 rows. Indexes exist on DeptID (clustered) and Salary (non‑clustered). The query is:

SELECT DeptID, AVG(Salary)
FROM Employees
WHERE HireDate > '2020-01-01'
GROUP BY DeptID
HAVING AVG(Salary) > 50000
ORDER BY DeptID;

Assuming 30 % of the rows satisfy the HireDate condition and there are 5 departments with roughly equal numbers of rows, estimate which index(es) the optimiser is likely to use and state how many rows will be examined during execution.

Accepted Answer

The optimiser will perform a full table scan of all 10,000 rows; the DeptID index may be used for grouping but does not reduce the rows read.

Gce Model Questions Vol1 2024 Computer Studies Question 33

Answer Options

About This Question

Master the Exam!