Solución ejercicios tema 6. Actualización

Ejercicio 1

SELECT * INTO nuevaempleados
FROM empleados;

Ejercicio 2

SELECT * INTO nuevaoficinas
FROM oficinas;

Ejercicio 3

SELECT * INTO nuevaproductos
FROM productos;

Ejercicio 4

SELECT * INTO nuevapedidos
FROM pedidos;

Ejercicio 5

UPDATE productos
SET precio = precio * 1.05 WHERE idfab = 'ACI';

También se puede poner precio = precio + precio*0.05

Ejercicio 6

INSERT INTO oficinas ( oficina, region, ciudad, objetivo )
VALUES ( 30, 'centro','Madrid', 100000 );

Como no asignamos valor a todos los campos, no hace falta poner todas las columnas en la lista de columnas. Los campos dir y ventas se rellenarán con el valor predeterminado.

¡Ojo! Si la tabla oficinas tiene definido en la columna dir el valor predeterminado 0, al intentar ejecutar la INSERT ocurrirá un error porque asigna 0 al campo dir , como dir es clave ajena, antes de insertar comprueba que el valor insertado en la clave ajena existe en la tabla empleados, y el empleado 0 no existe por lo que no puede insertar la oficina, el valor predeterminado de dir debe ser nulo.

Solución 2

INSERT INTO oficinas (oficina,region,ciudad,dir,objetivo,ventas)
VALUES (30, 'centro', 'Madrid', null, 100000,0) ;

Con esta solución nos aseguramos que el valor de dir sea nulo independientemente del valor predeterminado y nos aseguramos que ventas sea igual a cero.

Solución 3

INSERT INTO oficinas
VALUES (30, 'Madrid', 'centro', null, 100000,0) ;

En este caso como no especificamos una lista de columnas tenemos que poner los valores en el mismo orden que las columnas en vista diseño de la tabla.

Ejercicio 7

UPDATE empleados SET oficina = 30 WHERE oficina = 21;

Si ejecutamos esta sentencia antes de haber creado la oficina 30, el sistema nos devuelve un error.

Ejercicio 8

DELETE FROM pedidos WHERE rep = 105;

Ejercicio 9

Solución 1
DELETE FROM oficinas WHERE NOT EXISTS (SELECT *
FROM empleados WHERE empleados.oficina = oficinas.oficina);

Si la oficina no tiene empleados asignados, no existe ningún empleado con el número de esa oficina.

Solución 2
DELETE FROM oficinas WHERE oficina NOT IN (SELECT oficina
FROM empleados WHERE oficina IS NOT NULL) ;

También se puede ver como las oficinas cuyo número no se encuentra entre las oficinas asignados a los empleados. Con el NOT IN siempre tenemos que asegurarnos de que la subconsulta no devuelva nulos.

Solución 3
DELETE oficinas.*
FROM oficinas LEFT JOIN empleados
ON oficinas.oficina= empleados.oficina
WHERE empleados.numemp IS NULL ;

Otro planteamiento sería unir los empleados con sus oficinas y que también salgan las oficinas que no tienen empleados (por eso LEFT en vez de INNER) a partir de ahí seleccionamos las filas que no tienen valor en el campo numemp, estas son las no tienen ningún empleado relacionado. Como además el origen está basado en dos tablas es obligatorio poner oficinas.* para indicar que se tienen que borrar las filas de la tabla oficinas y no de empleados.

Ejercicio 10

UPDATE productos INNER JOIN nuevaproductos
ON ( productos.idfab = nuevaproductos.idfab) AND (productos.idproducto = nuevaproductos.idproducto)
SET productos.precio = nuevaproductos.precio;

Unimos la tabla de productos con la tabla nuevaproductos para tener en una misma fila el precio que queremos cambiar y el precio antiguo (el valor que queremos dejar).

Ejercicio 11

INSERT INTO oficinas
SELECT * FROM nuevaoficinas
WHERE oficina NOT IN (SELECT oficina FROM oficinas);

En este caso insertamos en oficinas las oficinas de nuevaoficinas cuyo número de oficina no está en oficinas (es decir las que se han borrado). Recuerda con NOT IN hay que asegurarse de que la subconsulta no devuelva nulos, en este caso como oficina es clave principal de la tabla oficinas no admite nulos por lo que no hace falta añadir nada más.

Ejercicio 12

INSERT INTO pedidos
SELECT * from nuevapedidos WHERE rep = 105;

Insertamos en pedidos los pedidos del empleados 105 que se encuentran en la tabla nuevapedidos.

Ejercicio 13

UPDATE empleados
SET oficina = 21 WHERE oficina = 30;

Si no hemos recuperado las oficinas borradas, no permitirá cambiar el campo oficina a 21 ya que la oficina 21 es de las que se han borrado en el ejercicio 9.