NEW
Font size
WorksheetsIntegrated project: Maji Ndogo part 3 [MCQ] (Version : 0)
Total questions: 10
Worksheet time: 2hrs 40mins
The following query results in 2,698 rows of data being retrieved, but the auditor_report table only has 1,620 rows. Analyse the query and select the reason why this discrepancy occurs. Hint: Think about the type of relationship between our tables.
SELECT
auditorRep.location_id,
visitsTbl.record_id,
Empl_Table.employee_name,
auditorRep.true_water_source_score AS auditor_score,
wq.subjective_quality_score AS employee_score
FROM auditor_report AS auditorRep
JOIN visits AS visitsTbl
ON auditorRep.location_id = visitsTbl.location_id
JOIN water_quality AS wq
ON visitsTbl.record_id = wq.record_id
JOIN employee as Empl_Table
ON Empl_Table.assigned_employee_id = visitsTbl.assigned_employee_id;
The water_quality table has incorrect record_id values causing an incorrect join with the visits table.
The visits table has multiple records for each location_id, which when joined with auditor_report, results in multiple records for each location_id.
The employee table has duplicate assigned_employee_id values leading to duplicate rows in the result set.
The auditor_report table has duplicate location_id values causing more rows to be retrieved than expected.
What is the function of Incorrect_records in the following query?
WITH Incorrect_records AS ( −− This CTE fetches all of the records with wrong scores
SELECT
auditorRep.location_id,
visitsTbl.record_id,
Empl_Table.employee_name,
auditorRep.true_water_source_score AS auditor_score,
wq.subjective_quality_score AS employee_score
FROM auditor_report AS auditorRep
JOIN visits AS visitsTbl
ON auditorRep.location_id = visitsTbl.location_id
JOIN water_quality AS wq
ON visitsTbl.record_id = wq.record_id
JOIN employee as Empl_Table
ON Empl_Table.assigned_employee_id = visitsTbl.assigned_employee_id
WHERE visitsTbl.visit_count =1
AND auditorRep.true_water_source_score != wq.subjective_quality_score)
SELECT employee_name,
count(employee_name)
FROM Incorrect_records
GROUP BY Employee_name;
Incorrect_records acts as a persistent storage structure, saving the records with different scores between auditor and employee in an intermediate result in the database for future queries.
Incorrect_records filters and organises records with different scores between auditor and employee, preparing a tailored dataset for the main query.
Incorrect_records creates a new permanent table in the database, storing the records with different scores between auditor and employee for further analysis.
Incorrect_records serves as a temporary result set to store aggregated data of records with different scores between auditor and employee for the main query.
In the suspect_list CTE, a subquery is used. What type of subquery is it, and what is its purpose in the query?
suspect_list AS (
SELECT employee_name, number_of_mistakes
FROM error_count
WHERE number_of_mistakes > (SELECT AVG(number_of_mistakes) FROM error_count))
The subquery is a scalar subquery used to calculate the average number_of_mistakes for comparison
The subquery is a multi-row subquery used to fetch a list of employee_name with above-average number_of_mistakes.
The subquery is a correlated subquery used to compare each number_of_mistakes to the average number_of_mistakes.
The subquery is a table subquery used to create a temporary table for data filtering.
A colleague proposed the following CTE as an alternative to the suspect_list we used previously, but it does not give the desired results. What will be the result of this subquery?
suspect_list AS (
SELECT ec1.employee_name, ec1.number_of_mistakes
FROM error_count ec1
WHERE ec1.number_of_mistakes >= (
SELECT AVG(ec2.number_of_mistakes)
FROM error_count ec2
WHERE ec2.employee_name = ec1.employee_name))
The subquery is a multi-row subquery that calculates the average mistakes for every employee_name.
The subquery is a table subquery designed to produce a virtual table capturing average mistakes for each employee_name.
The subquery is a correlated subquery that returns all of the employees that made errors.
The subquery is a scalar subquery that calculates a single average number of mistakes for all employees.
How is the relationship between the employee table and the visits table represented in the ERD?
There is no direct relationship between the employee table and the visits table.
employee has a 1-to-1 relationship with visits.
employee has a many-to-many relationship with visits.
employee has a 1-to-many relationship with visits.
Which contains has location_id as its primary key?
location
employee
global_water_access
data_dictionary
How would you modify the Incorrect_records CTE to join the well_pollution data?
WITH Incorrect_records AS (
SELECT
auditorRep.location_id,
visitsTbl.record_id,
Empl_Table.employee_name,
auditorRep.true_water_source_score AS auditor_score,
wq.subjective_quality_score AS employee_score,
auditorRep.statements AS statements
FROM auditor_report AS auditorRep
JOIN visits AS visitsTbl
ON auditorRep.location_id = visitsTbl.location_id
JOIN water_quality AS wq
ON visitsTbl.record_id = wq.record_id
JOIN employee as Empl_Table
ON Empl_Table.assigned_employee_id = visitsTbl.assigned_employee_id
WHERE visitsTbl.visit_count =1 AND auditorRep.true_water_source_score != wq.subjective_quality_score);
JOIN well_pollution ON visitsTbl.source_id = well_pollution.source_id
JOIN well_pollution ON auditorRep.location_id = well_pollution.location_id
JOIN well_pollution ON water_quality.subjective_quality_score = well_pollution.subjective_quality_score
JOIN well_pollution ON visitsTbl.record_id = well_pollution.record_id
Which employee just avoided our classification of having an above-average number of mistakes?
Hint: Use one of the queries we used to aggregate data from Incorrect_records.
Enitan Zuri
Lalitha Kaburi
Rudo Imani
Farai Nia
Which of the following “suspects” is connected to the following civilian statement: “Suspicion coloured villagers' descriptions of an official's aloof demeanour and apparent laziness. The reference to cash transactions casts doubt on their motives.”
Bello Azibo
Malachi Mavuso
Zuriel Matembo
Lalitha Kaburi
Consider the provided SQL query. What does it do?
SELECT
auditorRep.location_id,
visitsTbl.record_id,
auditorRep.true_water_source_score AS auditor_score,
wq.subjective_quality_score AS employee_score,
wq.subjective_quality_score - auditorRep.true_water_source_score AS score_diff
FROM auditor_report AS auditorRep
JOIN visits AS visitsTbl
ON auditorRep.location_id = visitsTbl.location_id
JOIN water_quality AS wq
ON visitsTbl.record_id = wq.record_id
WHERE (wq.subjective_quality_score - auditorRep.true_water_source_score) > 9;
The query retrieves the location_id, record_id, and water scores by JOINING the water_quality and visits table, and then calculates a difference in scores between the employee’s scores and the auditor’s scores.
The query retrieves the location_id, record_id, and water scores, and calculates a difference in scores between the employee’s scores and the auditor’s scores.
The query retrieves the auditor records where employees assigned very high scores to very poor water sources.
The query retrieves the auditor records where the auditor found all of the records with incorrect scores.
