Clase 29: Solución ejercicio de Vistas

05/10/2021 — Juanse Marquez

Les dejo la solución a los ejercicios de la clase anterior.

Vuelvo a copiar la consigna para facilitar la lectua.

Crear una vista que incluya solamente los alquileres de casas, de manera que pueda consultarse así:

CREATE VIEW alquileres_de_casas AS 
SELECT o.id, o.alta, o.superficie, o.precio_venta, o.fecha_venta, 
  p.descripcion as provincia, CONCAT(v.apellido,", ", v.nombre) as vendedor 
FROM operaciones o JOIN provincias p USING(id_provincia) 
JOIN vendedores v USING(id_vendedor) 
WHERE id_tipo_operacion = 1 AND id_tipo_propiedad = 6;

Mostrar todos los datos de los alquileres de casas dados de alta en el año 2004 (utilizar la vista).

SELECT * FROM alquileres_de_casas
WHERE YEAR(alta) = 2004;

Hay un error en la superficie de la propiedad que corresponde a la operación con id 9. Debería decir: 128. Corregirlo desde la vista. ¿Se puede? ¿Por qué? Constatar qué sucedió en la tabla base.

UPDATE alquileres_de_casas SET superficie = 128 WHERE id=9;
-- Sí, es posible, porque cumple con las restricciones. Para constatar:
SELECT superficie FROM operaciones WHERE id=9;

Eliminar la operación con id 9 desde la vista. ¿Se puede? ¿Por qué?

DELETE FROM alquileres_de_casas WHERE id=9;
-- ERROR 1395 (HY000): Can not delete from join view 'inmobiliaria.alquileres_de_casas'
-- No se puede: Para hacer DELETE, la vista debe tener una sola tabla base.

Eliminar la vista creada en el paso 1. ¿Se perderán datos? ¿Por qué?

DROP VIEW alquileres_de_casas;
-- No se perderán datos, porque la vista no tiene datos propios, por lo que los
-- datos de la tabla original no han sido afectados.

Crear una vista con el apellido y nombre de cada vendedor, y cuántas ventas y cuántos alquileres han concretado. Debe poder consultarse así:

CREATE VIEW resumen_vendedores AS 
SELECT v.apellido, v.nombre, 
(
  SELECT COUNT(*) FROM operaciones o
  WHERE o.id_tipo_operacion = 1 AND o.id_vendedor = v.id_vendedor
) as alquileres, 
(
  SELECT COUNT(*) FROM operaciones o 
  WHERE o.id_tipo_operacion = 2 AND o.id_vendedor = v.id_vendedor
) as ventas 
FROM vendedores v;

Utilizando la vista, consultar apellido y cantidad de ventas de los vendedores que han vendido más de 175 operaciones.

SELECT apellido, ventas FROM resumen_vendedores WHERE ventas > 175;

Utilizando la vista, consultar apellido, nombre y cantidad de operaciones de los vendedores que han realizado más de 350 operaciones en total.

SELECT apellido, nombre, ventas + alquileres as operaciones 
FROM resumen_vendedores 
WHERE ventas + alquileres > 350;

Modificar la vista, para que solamente incluya operaciones que involucren cocheras.

CREATE OR REPLACE VIEW resumen_vendedores AS 
SELECT v.apellido, v.nombre, 
(
  SELECT COUNT(*) FROM operaciones o
  WHERE o.id_tipo_operacion = 1 -- Alquiler
    AND o.id_tipo_propiedad = 1 -- Cochera
    AND o.id_vendedor = v.id_vendedor
) as alquileres, 
(
  SELECT COUNT(*) FROM operaciones o 
  WHERE o.id_tipo_operacion = 2 -- Venta
    AND o.id_tipo_propiedad = 1 --Cochera
    AND o.id_vendedor = v.id_vendedor
) as ventas 
FROM vendedores v;

La vendedora de apellido “Liberotti”, no se llama “María” sino “Marta”. Corregir desde la vista. ¿Se puede? ¿Por qué?

UPDATE resumen_vendedores SET nombre="Marta" WHERE apellido="Liberotti";
-- ERROR 1288 (HY000): The target table resumen_vendedores of the UPDATE is not updatable
-- No se puede, porque la vista incluye la función COUNT, por lo que no es
-- actualizable.

Eliminar desde la vista a la vendedora que no tiene ninguna operación. ¿Se puede? ¿Por qué?

DELETE FROM resumen_vendedores WHERE apellido="Pinelli";
-- ERROR 1288 (HY000): The target table resumen_vendedores of the DELETE is not updatable
-- No se puede, porque la vista no es actualizable y además involucra más de una 
-- tabla base.

Eliminar la vista.

DROP VIEW resumen_vendedores;

Etiquetas: clases, ejercicios, soluciones

Bases de Datos II

Clase 29: Solución ejercicio de Vistas