### Question 1: --- -------------------------------------------------- --- Given the following table:

sql CREATE TABLE `IMPUTATION` ( `ID` bigint (20) unsigned NOT NULL AUTO_INCREMENT, `Employee_code` bigint (20) unsigned DEFAULT NULL, `Activity` bigint (20) unsigned DEFAULT, `Hours` decimal (50,14) DEFAULT NULL, `Shared_id` varchar (128) NOT NULL, PRIMARY KEY (`ID`), KEY `Code_empleado_index` (` Code_empleado`), KEY `Activity_index` (` Activity`), KEY `Shared_id_index` (` Shared_id`), KEY `Emp_act_index` (` Employee_code`, `Activity`), KEY `Emp_act_qc_index` (` Employee_code`, `Activity`,` Fortnight`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; `` ''

In the hypothetical case of having an application that processes data related to employees. Taking into account the following:

- The app integrates with another external app that manages the allocation of hours of those employees. - Through an api receive the data of the imputations of the employees. - The imputation table contains the data of the hours that the employees in the different activities / projects / tasks of the company. - Massive data imports arrive through the api, both added and updates, and the systems do not share the same IDs (for the imputations) so when the integration was designed it was decided to use a code generated up to 128 characters that is unique and corresponds to the employee, date and exercise. ** This field would be Shared_id in the table above. ** - When data is received, the Shared_id and records that already exist are searched are updated while the rest are added.

With this in mind, one day it is discovered that there is an error in the code that is in charge of inserting / updating data. This error is causing there are duplicate records with the same Shared_id in the imputation table, even some cases where the number of hours does not match. The mistake is corrected at the code level but now we have to clean the imputation table. For this it is necessary to take into account that we must delete the duplicates leaving only the last record, which is supposed to contain the valid data.

Design a query that clears only the data that is wrong. Further, Do you think the table has a design error? If so, how would you improve it?

-------------------------------------------------- --- ### Question 2: --- ----------------------------: --------------------- ---- Given the following query

sql SELECT distinct (`TABLE_B` `field_4780`) AS` country`, `TABLE_A` `field_4302` AS` entity_name`, (Round (Sum (`TABLE_C` `field_3877`), 0)) AS` amount`, (Round (Sum (`TABLE_C` `field_3881`), 0)) AS` budget_deviation_amount`, (Round (Sum (`TABLE_C` `field_3878`), 0)) AS` budget_amount`, `TABLE_E``TEXT_FIELD_E` AS` Description` FROM `TABLE_C` INNER JOIN `TABLE_F` ON` TABLE_C` `field_3874` = `TABLE_F``id` INNER JOIN `TABLE_D` ON` TABLE_C` `field_3873` = `TABLE_D``id` INNER JOIN `TABLE_E` ON` TABLE_F``id` = `TABLE_E` `field_4024` LEFT JOIN `TABLE_G` ON ( `TABLE_E``field_4033` =` TABLE_G``id` AND `TABLE_E` `field_4034` =` TABLE_G``field_4015` AND `TABLE_E``TEXT_FIELD_E` =` TABLE_G``TEXT_FIELD_G` ) INNER JOIN `TABLE_H` ON` TABLE_D` `id` = `TABLE_H``field_4785` INNER JOIN `TABLE_A` ON` TABLE_D``id` = `TABLE_A` `field_4052` INNER JOIN `TABLE_B` ON` TABLE_H` `field_4786` = `TABLE_B``id` WHERE ( UPPER (`TABLE_A` `field_4302`) = 'ENTITY' OR `TABLE_B` `field_4302` like 'SECONDARY%' ) AND `TABLE_A` `field_4307` = 1 GROUP BY `TABLE_E` `field_4033`, `TABLE_G`` field_4019` ORDER BY `TABLE_A` `field_4302` ASC LIMIT 1000; `` ''

Briefly describe, how you would optimize the query, what things you consider to be should be changed and what indices would you add to the tables involved in the same (if you consider that one should be added).