City Guilds Model Questions Vol1 2022 Information Technology Question 35
Practice objective / multiple choice question 35 from the 2022 City Guilds Model Questions Vol1 Information Technology examination.
Consider the relational schema:\n\nEmployees(EmpID, DeptID, Salary) - 10 000 rows, DeptID values uniformly distributed over 20 departments.\nDepartments(DeptID, DeptName) - 20 rows, DeptID is the primary key.\n\nA query requests the department name together with the average salary of employees earning more than $50 000, grouped by department:\n\nSELECT D.DeptName, AVG(E.Salary)\nFROM Employees E JOIN Departments D ON E.DeptID = D.DeptID\nWHERE E.Salary > 50000\nGROUP BY D.DeptName;\n\nAssume that 30 % of the Employees satisfy the salary condition and that the average salary of those qualifying employees is $65 000, identical for each department. Estimate (a) the number of rows produced by the join before grouping, (b) the number of result groups, and (c) which join order (Employees first or Departments first) yields the lower I/O cost when using a block‑nested‑loop join with a block size of 100 rows. Provide your answers.
Answer Options
About This Question
This is City Guilds Model Questions Vol1 2022 Information Technology Question 35. It is one of the objective questions from the 2022 City Guilds Model Questions Vol1 Information Technology examination.
Difficulty level: Medium .
Master the Exam!
You've seen a preview, but there are thousands more questions plus AI tutor to break down complex solutions.