Bases de Datos II

-- 5) Mostrar id, nombre, apellido y fecha de la primera venta de una casa para
-- cada uno de los vendedores.
SELECT v.id_vendedor, v.nombre, v.apellido, (
    SELECT MIN(fecha_venta) FROM operaciones o 
    WHERE o.id_vendedor = v.id_vendedor AND o.id_tipo_operacion = (
        SELECT id_tipo_operacion FROM tipo_operacion 
        WHERE descripcion LIKE "Venta"
    ) 
    AND o.id_tipo_propiedad = (
        SELECT id_tipo_propiedad FROM tipo_propiedad 
        WHERE descripcion LIKE "Casa"
    ) 
    GROUP BY o.id_vendedor
) as primera_venta 
FROM vendedores v;

-- 6) Mostrar, para cada uno de los vendedores:
-- id, apellido y nombre
-- la cantidad de operaciones a su cargo
-- la cantidad de operaciones a su cargo anteriores al 1º de enero de 2005.
SELECT v.id_vendedor, v.nombre, v.apellido, COUNT(o.id), (
  SELECT COUNT(op.id)
  FROM operaciones op 
  WHERE op.fecha_venta < '2005-01-01' AND o.id_vendedor = op.id_vendedor   
  GROUP BY op.id_vendedor
) as antes_de_2005 
FROM vendedores v LEFT JOIN operaciones o using(id_vendedor) 
GROUP BY id_vendedor;

-- Con JOIN:
SELECT v.id_vendedor, v.nombre, v.apellido, COUNT(o.id), COUNT(op.id) as antes_2005
FROM vendedores v 
LEFT JOIN operaciones o using(id_vendedor) 
LEFT JOIN operaciones op ON o.id = op.id AND op.fecha_venta < '2005-01-01' 
GROUP BY o.id_vendedor;

-- 7) Mostrar id, apellido y nombre de cada vendedor, indicando para cada uno de 
-- ellos qué porcentaje de las ventas se realizaron antes de 2005. La propuesta es 
-- intentar utilizar como fuente de datos la respuesta al ejercicio anterior, y no 
-- la BD original.
SELECT id_vendedor, apellido, nombre,
       antes_de_2005/total*100 as porcentaje_antes_2005 
FROM (
    -- Respuesta al ejercicio anterior
    SELECT v.id_vendedor, v.nombre, v.apellido, COUNT(o.id) as total, (
        SELECT COUNT(op.id) 
        FROM operaciones op 
        WHERE op.fecha_venta < '2005-01-01' AND o.id_vendedor = op.id_vendedor 
        GROUP BY op.id_vendedor
    ) as antes_de_2005 
    FROM vendedores v LEFT JOIN operaciones o using(id_vendedor) 
    GROUP BY id_vendedor
    -- Fin respuesta al ejercicio anterior
) as t;

-- Salida un poco más "decorada":
SELECT id_vendedor, CONCAT(apellido, ", ",nombre) as vendedor, 
       CONCAT(ROUND(antes_de_2005/total*100),'%') as porcentaje_antes_2005 
FROM (
    -- Respuesta al ejercicio anterior
    SELECT v.id_vendedor, v.nombre, v.apellido, COUNT(o.id) as total, (
        SELECT COUNT(op.id) 
        FROM operaciones op 
        WHERE op.fecha_venta < '2005-01-01' AND o.id_vendedor = op.id_vendedor 
        GROUP BY op.id_vendedor
    ) as antes_de_2005 
    FROM vendedores v LEFT JOIN operaciones o using(id_vendedor) 
    GROUP BY id_vendedor
    -- Fin respuesta al ejercicio anterior
) as t;

-- 8)Mostrar:
--  id de tipo de propiedad
--  descripción de tipo de propiedad
--  cantidad de operaciones en las que interviene ese tipo de propiedad
--  para todos los tipos de propiedad cuya cantidad de operaciones sea superior
-- a la cantidad de operaciones en las que intervienen las cocheras.

-- Consulta con pseudocódigo
SELECT id_tipo_propiedad, tp.descripcion , COUNT(o.id) 
FROM tipo_propiedad tp LEFT JOIN operaciones o using(id_tipo_propiedad) 
GROUP BY id_tipo_propiedad 
HAVING COUNT(o.id) > (
    -- Cantidad de operaciones en las que intervienen las cocheras
);

-- Cantidad de operaciones en las que intervienen las cocheras:
SELECT COUNT(*) FROM operaciones 
WHERE id_tipo_propiedad = (
  SELECT id_tipo_propiedad from tipo_propiedad WHERE descripcion LIKE "Cochera"
);

SELECT id_tipo_propiedad, tp.descripcion , COUNT(o.id) 
FROM tipo_propiedad tp LEFT JOIN operaciones o using(id_tipo_propiedad) 
GROUP BY id_tipo_propiedad HAVING COUNT(o.id) > (
    -- Cantidad de operaciones en las que intervienen las cocheras:
    SELECT COUNT(*) from operaciones 
    WHERE id_tipo_propiedad = (
        SELECT id_tipo_propiedad 
        FROM tipo_propiedad 
        WHERE descripcion LIKE "Cochera"
    )
);