1.
Añadir a la oficina 40 otro empleado, Luis Valverde, con número de empleado 436, con los mismos
datos que el anterior pero su jefe será el director de la oficina 40 (no sabemos qué número tiene).
INSERT INTO empleados (numemp, nombre, titulo, contrato, ventas, cuota, oficina, jefe)
SELECT 436, 'Luis Valverde','Vendedor', GETDATE(), 0, 1200.45, 40, dir
FROM oficinas WHERE oficina = 40;
2. Pasar los pedidos de octubre 1989 a diciembre 2008. (3 filas afectadas)
UPDATE pedidos SET fechapedido=DATEADD(month,230,fechapedido)
WHERE year(fechapedido)=1989 and month(fechapedido)=10;
3. Queremos actualizar el importe de los pedidos del mes actual con el precio almacenado en la tabla
productos.
SELECT codigo, numpedido,fechapedido,cant, importe,importe/cant AS [precio pedido],precio
FROM pedidos inner join productos ON fab=idfab and producto = idproducto
WHERE YEAR(fechapedido)=YEAR(GETDATE()) and MONTH(fechapedido)=MONTH(GETDATE())
Actualizar después la tabla de pedidos cambiando los importes para que el precio unitario corresponda con el
precio del producto. Los pedidos de los productos que no tienen precio se quedarán como estaban. (3 filas
afectadas)
UPDATE pedidos SET importe=cant*precio
FROM pedidos inner join productos ON fab=idfab and producto = idproducto
WHERE year(fechapedido)=2008 and month(fechapedido)=12 AND precio IS NOT NULL;
4. Se ven algunos productos que no tienen precio, ahora vamos a actualizar el precio de estos productos
con el precio medio utilizado en los pedidos donde aparecen. La primera SELECT saca los productos que
queremos actualizar con los pedidos correspondientes.
SELECT idfab, idproducto, precio, codigo, numpedido,fechapedido,importe, importe/cant
FROM productos left join pedidos on idfab=fab AND idproducto=producto
WHERE precio IS NULL;
UPDATE productos SET precio = (SELECT ROUND(AVG(importe/cant),2) FROM pedidos WHERE fab=idfab
AND producto=idproducto)
WHERE precio IS NULL
Curso: SQL ESTÁNDAR Básico ---- Ejercicios 6 (Respuestas) [Hoja 1 de 4]
L.I. Raymundo Lumbreras López
5. Eliminar los pedidos del representante Luis Antonio. (2 filas afectadas)
DELETE FROM pedidos
WHERE rep IN (SELECT numemp FROM empleados
WHERE nombre = 'Luis Antonio');
6. Eliminar las oficinas que no tengan empleados. (11 filas afectadas)
DELETE oficinas
WHERE NOT EXISTS (SELECT *
FROM empleados WHERE empleados.oficina = oficinas.oficina);
7. Intenta eliminar el empleado 102. Te saldrá un error : "Instrucción DELETE en conflicto con la
restricción..."
DELETE empleados WHERE numemp=102;
Reflexiona sobre el error y elabora una consulta que liste los empleados que pueden dar problemas (aquí no
se trata de utilizar operaciones aprendidas en este tema sino de practicar la integridad referencial y de paso
recordar instrucciones vistas en temas anteriores).
SELECT * from empleados
WHERE numemp IN (select jefe from empleados)
UNION ALL
SELECT * FROM empleados
WHERE numemp IN (SELECT dir FROM oficinas)
UNION ALL
SELECT * FROM empleados
WHERE numemp IN (SELECT rep FROM pedidos)
UNION ALL
SELECT * FROM empleados
WHERE numemp IN (SELECT repclie FROM clientes)
ORDER BY numemp
Elabora ahora una consulta que liste los que se pueden borrar sin problemas.
SELECT numemp FROM empleados
EXCEPT
SELECT numemp from empleados
WHERE numemp IN (select jefe from empleados)
EXCEPT
SELECT numemp FROM empleados
WHERE numemp IN (SELECT dir FROM oficinas)
EXCEPT
SELECT numemp FROM empleados
WHERE numemp IN (SELECT rep FROM pedidos)
EXCEPT
Curso: SQL ESTÁNDAR Básico ---- Ejercicios 6 (Respuestas) [Hoja 2 de 4]
L.I. Raymundo Lumbreras López
SELECT numemp FROM empleados
WHERE numemp IN (SELECT repclie FROM clientes)
ORDER BY numemp
Borra el primero de la lista, éste sí lo puedes borrar.
DELETE empleados WHERE numemp=112;
8. Eliminar los pedidos de productos de ACI cuyo precio de venta en el pedido no corresponda con el
precio unitario del producto de la tabla de productos. (4 filas afectadas)
DELETE FROM pedidos
WHERE fab = 'ACI' AND Importe/cant <> (SELECT precio FROM productos WHERE fab = idfab AND idproducto
= producto);
9. Ahora vamos a recuperar las tablas tal cual estaban al principio, para ello utilizaremos las copias
realizadas al principio de Nuevaoficinas, etc. Lo más cómodo será vaciar las tablas y rellenarlas de nuevo
con los datos de las tablas Nueva...
Empieza por eliminar los datos de las tablas. Utilizamos TRUNCATE en la tabla pedidos porque no está
referenciada y para que el contador empiece desde 1 otra vez
TRUNCATE TABLE pedidos
DELETE productos
DELETE clientes
UPDATE oficinas SET dir = NULL; -- Para poder borrar los empleados
UPDATE empleados SET oficina=NULL, jefe=NULL; -- Para poder borrar las oficinas y los empleados
respectivamente.
DELETE oficinas;
DELETE empleados;
Curso: SQL ESTÁNDAR Básico ---- Ejercicios 6 (Respuestas) [Hoja 3 de 4]
L.I. Raymundo Lumbreras López
Inserta los datos de las tablas Nueva.. a las tablas normales.
INSERT INTO oficinas (oficina, ciudad, region,objetivo,ventas)
SELECT oficina, ciudad, region,objetivo,ventas
FROM Nuevaoficinas;
No podemos rellenar todavía la columna Dir ya que no tenemos los empleados dado de alta.
INSERT INTO empleados (numemp, nombre, edad, oficina,titulo,contrato,cuota,ventas)
SELECT numemp, nombre, edad, oficina,titulo,contrato,cuota,ventas
FROM NuevaEmpleados;
Lo mismo pasa con la columna jefe.
UPDATE empleados SET jefe=nueva.jefe
FROM empleados INNER JOIN NuevaEmpleados nueva ON empleados.numemp=nueva.numemp
Estas dos últimas sentencias se podían haber resumido en una:
INSERT INTO empleados
SELECT *
FROM NuevaEmpleados;
Las hemos dejado en dos porque con otros SQLs no se podría hacer en un sólo paso.
UPDATE oficinas SET dir = (SELECT dir FROM Nuevaoficinas nueva
WHERE oficinas.oficina=nueva.oficina)
INSERT INTO clientes (numclie,nombre,repclie,limitecredito)
SELECT numclie,nombre,repclie,limitecredito
FROM Nuevaclientes;
INSERT INTO productos
SELECT *
FROM Nuevaproductos;
INSERT INTO pedidos (numpedido,fechapedido,clie,rep,fab,producto,cant,importe)
SELECT numpedido,fechapedido,clie,rep,fab,producto,cant,importe
FROM Nuevapedidos;
Curso: SQL ESTÁNDAR Básico ---- Ejercicios 6 (Respuestas) [Hoja 4 de 4]
L.I. Raymundo Lumbreras López
