Ver el código de «Solución á practica DML sobre a base de datos traballadores»

= Solucións =
== Tarefa 1.1 ==
:<syntaxhighlight lang="sql" line enclose="div" highlight="" >
 insert into centro
      values( 40,'FRANQUICIA LUGO', 'C/PROGRESO, 8 - LUGO' ) ;
</syntaxhighlight>

== Tarefa 1.2 ==
:<syntaxhighlight lang="sql" line enclose="div" highlight="" >
insert into empregado (empNumero, empDepartamento, empExtension, empDataNacemento,
	empDataIngreso, empSalario, empComision, empFillos, empNome)
values (750,110,25,'1990-02-12',curdate(),825,50,1,'BARCIA, ANGELES'),
	(751,110,25,'1985-10-22',subdate(curdate(),15),900,null,0,'MENDEZ, RICARDO'),
	(752,120,45,'1992-05-09',null,1200,150,2,'BERNARDEZ, LUCIA'),
	(753,100,200,'1980-11-05',curdate(),1000,300,1,'VALIN, EVA');
</syntaxhighlight>

== Tarefa 1.3 ==
:<syntaxhighlight lang="sql" line enclose="div" highlight="" >
update empregado
set empSalario = round(empSalario*1.05,2), 
	empComision = round(empComision*1.065,2);

-- Si da error
-- Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
-- se puede solucionar ejecutando previamente
-- SET SQL_SAFE_UPDATES = 0;
</syntaxhighlight>

== Tarefa 1.4 ==
:<syntaxhighlight lang="sql" line enclose="div" highlight="" >
update empregado
set empDataIngreso = adddate(last_day(curdate()),1);
where empNumero = 752 ;
</syntaxhighlight>

== Tarefa 1.5 ==
:<syntaxhighlight lang="sql" line enclose="div" highlight="" >
update empregado
set empSalario = round(empSalario*1.02,2)
where empDepartamento = 120 ;
</syntaxhighlight>

== Tarefa 1.6 ==
:<syntaxhighlight lang="sql" line enclose="div" highlight="" >
update empregado
set empComision=ifnull(empComision,0)+50
where empDepartamento in (select depNumero
				 from departamento
				 where depCentro in (select cenNumero
							from centro
							where cenNome='SEDE CENTRAL'));
</syntaxhighlight>
== Tarefa 1.7 ==
:<syntaxhighlight lang="sql" line enclose="div" highlight="" >
update departamento
set depPresuposto = depPresuposto-depPresuposto*0.10
order by depPresuposto desc
limit 1 ;
</syntaxhighlight>

:<syntaxhighlight lang="sql" line enclose="div" highlight="" >
-- Unha alternativa en MySQL, empregando variables de usuario:
select @mayor:=max(depPresuposto) from departamento;	
update departamento
set depPresuposto=depPresuposto-depPresuposto*0.10
where depPresuposto = @mayor;
</syntaxhighlight>

== Tarefa 1.8 ==
:<syntaxhighlight lang="sql" line enclose="div" highlight="" >
delete from empregado
where empNumero = 380  ;
</syntaxhighlight>

== Tarefa 1.9 ==
:<syntaxhighlight lang="sql" line enclose="div" highlight="" >
delete from empregado
where timestampdiff(year, empDataNacemento, curdate())>=60;
-- Outra forma de facelo:
delete from  empregado
where  date_add(empDataNacemento, interval 60 year)<curdate();
</syntaxhighlight>

== Tarefa 1.10 ==
:<syntaxhighlight lang="sql" line enclose="div" highlight="" >
delete em, de
from  departamento as de join empregado as em
where de.depNumero=em.empDepartamento
	and de.depNumero=121;
</syntaxhighlight>

== Tarefa 1.11 ==
:<syntaxhighlight lang="sql" line enclose="div" highlight="" >
-- creación da táboa temporal 
create temporary table empregado_120 like empregado;
-- inserción de datos
insert into empregado_120
select * 
from empregado
where empDepartamento = 120;
</syntaxhighlight>