-- EJERCICIO 1--------------------------------------------------------------------------------------------------------------------------------------------------
-- NUMERO DE IMMMUEBLES POR ZONA

SELECT CM.NOMBRE||' - '|| P.NOMBRE ||' - '|| C.NOMBRE AS LUGAR, M.NUM AS "NUMERO DE IMMUEBLES"
FROM (SELECT I.ID_COMARCA, COUNT(*) AS NUM
FROM IMMUEBLES I
GROUP BY I.ID_COMARCA) M,
COMARCAS C, PROVINCIAS P, COMUNIDADES CM
WHERE M.ID_COMARCA=C.ID AND
P.ID=C.ID_PROVINCIA AND
CM.ID=P.ID_COMUNIDAD
ORDER BY CM.NOMBRE, P.NOMBRE, C.NOMBRE ASC

-- NUMERO DE IMMMUEBLES POR TIPO

SELECT T.NOMBRE AS TIPO, M.NUM AS "NUMERO DE IMMUEBLES"
FROM (SELECT I.ID_TIPO, COUNT(*) AS NUM
FROM IMMUEBLES I
GROUP BY I.ID_TIPO) M,
TIPOS T
WHERE M.ID_TIPO=T.ID



-- EJERCICIO 2--------------------------------------------------------------------------------------------------------------------------------------------------------
-- Muestra solo los immuebles que han bajado el precio, así como la diferencia de precio. 

SELECT CM.NOMBRE ||' - '|| P.NOMBRE ||' - '|| C.NOMBRE AS LUGAR, I.CODIGO, I.PRECIO_INICIO AS "PRECIO INICIAL", O.PRECIO_VENTA AS "PRECIO FINAL", I.PRECIO_INICIO - O.PRECIO_VENTA AS DIFERENCIA
FROM IMMUEBLES I, OFERTAS O, COMARCAS C, PROVINCIAS P, COMUNIDADES CM
WHERE O.CODIGO_IMMUEBLE = I.CODIGO AND 
O.VENDIDO=1 AND
I.ID_COMARCA=C.ID AND
C.ID_PROVINCIA=P.ID AND
P.ID_COMUNIDAD=CM.ID and
O.PRECIO_VENTA < I.PRECIO_INICIO 
ORDER BY DIFERENCIA ASC

-- NÚMERO DE IMMUEBLES QUE HAN BAJADO SU PRECIO POR TIPO

SELECT T.NOMBRE, N.NUM AS "NUMERO DE IMMUEBLES"
FROM (SELECT I.ID_TIPO, COUNT(*) AS NUM 
FROM IMMUEBLES I, OFERTAS O
WHERE O.CODIGO_IMMUEBLE = I.CODIGO AND 
O.VENDIDO=1 AND
O.PRECIO_VENTA < I.PRECIO_INICIO 
GROUP BY I.ID_TIPO) N,
TIPOS T
WHERE N.ID_TIPO=T.ID



--EJERCICIO 3-------------------------------------------------------------------------------------------------------------------------------------------------------
-- Precios máximos, mínimos y medios por ZONA


SELECT CM.NOMBRE||' - '|| P.NOMBRE ||' - '|| C.NOMBRE AS LUGAR, M.MAXIMO, M.MEDIA, M.MINIMO
FROM (SELECT I.ID_COMARCA, MAX(I.PRECIO_INICIO) AS MAXIMO, MIN(I.PRECIO_INICIO) AS MINIMO, ROUND(AVG(I.PRECIO_INICIO),2) AS MEDIA
FROM IMMUEBLES I
GROUP BY I.ID_COMARCA) M,
COMARCAS C, PROVINCIAS P, COMUNIDADES CM
WHERE M.ID_COMARCA=C.ID AND
P.ID=C.ID_PROVINCIA AND
CM.ID=P.ID_COMUNIDAD
ORDER BY CM.NOMBRE, P.NOMBRE, C.NOMBRE ASC

-- Precios máximos, mínimos y medios por TIPO

SELECT T.NOMBRE AS TIPO, M.MAXIMO, M.MEDIA, M.MINIMO
FROM (SELECT I.ID_TIPO, MAX(I.PRECIO_INICIO) AS MAXIMO, MIN(I.PRECIO_INICIO) AS MINIMO, ROUND(AVG(I.PRECIO_INICIO),2) AS MEDIA
FROM IMMUEBLES I
GROUP BY I.ID_TIPO) M,
TIPOS T
WHERE M.ID_TIPO=T.ID 



-- EJERCICIO 4-------------------------------------------------------------------------------------------------------------------------------------------------------
-- Precios de venta real máximos, medios y mínimos por zona

SELECT CM.NOMBRE||' - '|| P.NOMBRE ||' - '|| C.NOMBRE AS LUGAR, M.MAXIMO, M.MEDIA, M.MINIMO
FROM (SELECT I.ID_COMARCA, MAX(O.PRECIO_VENTA) AS MAXIMO,MIN(O.PRECIO_VENTA) AS MINIMO, ROUND(AVG(O.PRECIO_VENTA),2) AS MEDIA
		FROM OFERTAS O, IMMUEBLES I
		WHERE I.CODIGO=O.CODIGO_IMMUEBLE
GROUP BY I.ID_COMARCA) M,
COMARCAS C, PROVINCIAS P, COMUNIDADES CM
WHERE M.ID_COMARCA=C.ID AND
P.ID=C.ID_PROVINCIA AND
CM.ID=P.ID_COMUNIDAD
ORDER BY CM.NOMBRE, P.NOMBRE, C.NOMBRE ASC

-- Precios de venta real máximos, medios y mínimos por tipo

SELECT T.NOMBRE AS TIPO, M.MAXIMO, M.MEDIA, M.MINIMO
FROM (SELECT I.ID_TIPO, MAX(O.PRECIO_VENTA) AS MAXIMO,MIN(O.PRECIO_VENTA) AS MINIMO, ROUND(AVG(O.PRECIO_VENTA),2) AS MEDIA
		FROM OFERTAS O, IMMUEBLES I
		WHERE I.CODIGO=O.CODIGO_IMMUEBLE
GROUP BY I.ID_TIPO) M,
TIPOS T
WHERE M.ID_TIPO=T.ID




--EJERCICIO 5------------------------------------------------------------------------------------------------------------------------------------------------------
-- Diferencia entre los precios de inicio y de venta real.

SELECT CM.NOMBRE ||' - '|| P.NOMBRE ||' - '|| C.NOMBRE AS LUGAR, I.CODIGO, T.NOMBRE AS TIPO, I.HABITACIONES, I.METROS, I.PRECIO_INICIO AS "PRECIO INICIAL", O.PRECIO_VENTA AS "PRECIO FINAL", I.PRECIO_INICIO - O.PRECIO_VENTA AS DIFERENCIA
FROM IMMUEBLES I, OFERTAS O, COMARCAS C, PROVINCIAS P, COMUNIDADES CM, TIPOS T
WHERE O.CODIGO_IMMUEBLE = I.CODIGO AND 
O.VENDIDO=1 AND
I.ID_COMARCA=C.ID AND
C.ID_PROVINCIA=P.ID AND
P.ID_COMUNIDAD=CM.ID AND 
T.ID=I.ID_TIPO
ORDER BY DIFERENCIA ASC



--EJERCICIO 6-------------------------------------------------------------------------------------------------------
-- Metros cuadrados máximos, mínimos y medios por ZONA


SELECT CM.NOMBRE||' - '|| P.NOMBRE ||' - '|| C.NOMBRE AS LUGAR, M.MAXIMO, M.MEDIA, M.MINIMO
FROM (SELECT I.ID_COMARCA, MAX(I.METROS) AS MAXIMO, MIN(I.METROS) AS MINIMO, ROUND(AVG(I.METROS),2) AS MEDIA
FROM IMMUEBLES I
GROUP BY I.ID_COMARCA) M,
COMARCAS C, PROVINCIAS P, COMUNIDADES CM
WHERE M.ID_COMARCA=C.ID AND
P.ID=C.ID_PROVINCIA AND
CM.ID=P.ID_COMUNIDAD

-- Metros cuadrados máximos, mínimos y medios por TIPOLOGÍA

SELECT T.NOMBRE AS LUGAR, M.MAXIMO, M.MEDIA, M.MINIMO
FROM (SELECT I.ID_TIPO, MAX(I.METROS) AS MAXIMO, MIN(I.METROS) AS MINIMO, ROUND(AVG(I.METROS),2) AS MEDIA
FROM IMMUEBLES I
GROUP BY I.ID_TIPO) M,
TIPOS T 
WHERE T.ID=M.ID_TIPO



-- EJERCICIO 7-------------------------------------------------------------------------------------------------------------------------------------------------
-- NUMERO DE PISOS POR NÚMERO DE HABITACIONES

SELECT I.HABITACIONES, COUNT(*) AS NUM
FROM IMMUEBLES I
GROUP BY I.HABITACIONES
ORDER BY I.HABITACIONES ASC

-- La cantidad de pisos por zona y tipología está desarrollado en otros apartados.




-- EJERCICIO 8---------------------------------------------------------------------------------------------------------------------------------------------------------------

--TIEMPO MEDIO DE VENTA POR ZONA

SELECT DISTINCT(CM.NOMBRE ||' - '|| P.NOMBRE ||' - '|| C.NOMBRE) AS LUGAR, X.MESES
FROM (SELECT I.ID_COMARCA, ROUND(AVG(O.ID_FECHA - DECODE(CA.P_ENERO2006,NULL,DECODE(CA.P_FEBRERO2006,NULL,DECODE(CA.P_MARZO2006,NULL,DECODE(CA.P_ABRIL2006,NULL,DECODE(CA.P_MAYO2006,NULL,DECODE(CA.P_JUNIO2006,NULL,DECODE(CA.P_JULIO2006,NULL,DECODE(CA.P_AGOSTO2006,NULL,DECODE(CA.P_SEPTIEMBRE2006,NULL,DECODE(CA.P_OCTUBRE2006,NULL,DECODE(CA.P_NOVIEMBRE2006,NULL,DECODE(CA.P_DICEMBRE2006,NULL,DECODE(CA.P_ENERO2007,NULL,DECODE(CA.P_FEBRERO2007,NULL,DECODE(CA.P_MARZO2007,NULL,DECODE(CA.P_ABRIL2007,NULL,DECODE(CA.P_MAYO2007,NULL,DECODE(CA.P_JUNIO2007,NULL,DECODE(CA.P_JULIO2007,NULL,DECODE(CA.P_AGOSTO2007,NULL,DECODE(CA.P_SEPTIEMBRE2007,NULL,DECODE(CA.P_OCTUBRE2007,NULL,DECODE(CA.P_NOVIEMBRE2007,NULL,DECODE(CA.P_DICEMBRE2007,NULL,0,24),23),22),21),20),19),18),17),16),15),14),13),12),11),10),9),8),7),6),5),4),3),2),1 )),2) AS MESES
FROM IMMUEBLES I, CATALOGO CA, OFERTAS O
WHERE CA.CODIGO_IMMUEBLE=I.CODIGO AND
I.CODIGO=O.CODIGO_IMMUEBLE 
GROUP BY I.ID_COMARCA) X,
IMMUEBLES I, COMUNIDADES CM, PROVINCIAS P, COMARCAS C
WHERE 
X.ID_COMARCA=I.ID_COMARCA AND
I.ID_COMARCA=C.ID AND
C.ID_PROVINCIA=P.ID AND
P.ID_COMUNIDAD=CM.ID 
ORDER BY LUGAR ASC


--TIEMPO MEDIO DE VENTA POR TIPO

SELECT T.NOMBRE, X.MESES AS "MEDIA DE MESES EN VENTA"
FROM (SELECT I.ID_TIPO, ROUND(AVG(O.ID_FECHA - DECODE(CA.P_ENERO2006,NULL,DECODE(CA.P_FEBRERO2006,NULL,DECODE(CA.P_MARZO2006,NULL,DECODE(CA.P_ABRIL2006,NULL,DECODE(CA.P_MAYO2006,NULL,DECODE(CA.P_JUNIO2006,NULL,DECODE(CA.P_JULIO2006,NULL,DECODE(CA.P_AGOSTO2006,NULL,DECODE(CA.P_SEPTIEMBRE2006,NULL,DECODE(CA.P_OCTUBRE2006,NULL,DECODE(CA.P_NOVIEMBRE2006,NULL,DECODE(CA.P_DICEMBRE2006,NULL,DECODE(CA.P_ENERO2007,NULL,DECODE(CA.P_FEBRERO2007,NULL,DECODE(CA.P_MARZO2007,NULL,DECODE(CA.P_ABRIL2007,NULL,DECODE(CA.P_MAYO2007,NULL,DECODE(CA.P_JUNIO2007,NULL,DECODE(CA.P_JULIO2007,NULL,DECODE(CA.P_AGOSTO2007,NULL,DECODE(CA.P_SEPTIEMBRE2007,NULL,DECODE(CA.P_OCTUBRE2007,NULL,DECODE(CA.P_NOVIEMBRE2007,NULL,DECODE(CA.P_DICEMBRE2007,NULL,0,24),23),22),21),20),19),18),17),16),15),14),13),12),11),10),9),8),7),6),5),4),3),2),1 )),2) AS MESES
FROM IMMUEBLES I, CATALOGO CA, OFERTAS O
WHERE CA.CODIGO_IMMUEBLE=I.CODIGO AND
I.CODIGO=O.CODIGO_IMMUEBLE 
GROUP BY I.ID_TIPO) X,
TIPOS T
WHERE 
X.ID_TIPO=T.ID
ORDER BY T.NOMBRE ASC


-- PRUEBA MESES EN OFERTA

SELECT I.CODIGO, I.PRECIO_INICIO, O.ID_FECHA - DECODE(CA.P_ENERO2006,NULL,DECODE(CA.P_FEBRERO2006,NULL,DECODE(CA.P_MARZO2006,NULL,DECODE(CA.P_ABRIL2006,NULL,DECODE(CA.P_MAYO2006,NULL,DECODE(CA.P_JUNIO2006,NULL,DECODE(CA.P_JULIO2006,NULL,DECODE(CA.P_AGOSTO2006,NULL,DECODE(CA.P_SEPTIEMBRE2006,NULL,DECODE(CA.P_OCTUBRE2006,NULL,DECODE(CA.P_NOVIEMBRE2006,NULL,DECODE(CA.P_DICEMBRE2006,NULL,DECODE(CA.P_ENERO2007,NULL,DECODE(CA.P_FEBRERO2007,NULL,DECODE(CA.P_MARZO2007,NULL,DECODE(CA.P_ABRIL2007,NULL,DECODE(CA.P_MAYO2007,NULL,DECODE(CA.P_JUNIO2007,NULL,DECODE(CA.P_JULIO2007,NULL,DECODE(CA.P_AGOSTO2007,NULL,DECODE(CA.P_SEPTIEMBRE2007,NULL,DECODE(CA.P_OCTUBRE2007,NULL,DECODE(CA.P_NOVIEMBRE2007,NULL,DECODE(CA.P_DICEMBRE2007,NULL,0,24),23),22),21),20),19),18),17),16),15),14),13),12),11),10),9),8),7),6),5),4),3),2),1 ) AS "MESES EN OFERTA" 
FROM IMMUEBLES I, CATALOGO CA, OFERTAS O
WHERE CA.CODIGO_IMMUEBLE=I.CODIGO AND
I.CODIGO=O.CODIGO_IMMUEBLE

-- PRUEBA MESES DE OFERTA Y VENTA
SELECT I.CODIGO, I.PRECIO_INICIO, DECODE(CA.P_ENERO2006,NULL,DECODE(CA.P_FEBRERO2006,NULL,DECODE(CA.P_MARZO2006,NULL,DECODE(CA.P_ABRIL2006,NULL,DECODE(CA.P_MAYO2006,NULL,DECODE(CA.P_JUNIO2006,NULL,DECODE(CA.P_JULIO2006,NULL,DECODE(CA.P_AGOSTO2006,NULL,DECODE(CA.P_SEPTIEMBRE2006,NULL,DECODE(CA.P_OCTUBRE2006,NULL,DECODE(CA.P_NOVIEMBRE2006,NULL,DECODE(CA.P_DICEMBRE2006,NULL,DECODE(CA.P_ENERO2007,NULL,DECODE(CA.P_FEBRERO2007,NULL,DECODE(CA.P_MARZO2007,NULL,DECODE(CA.P_ABRIL2007,NULL,DECODE(CA.P_MAYO2007,NULL,DECODE(CA.P_JUNIO2007,NULL,DECODE(CA.P_JULIO2007,NULL,DECODE(CA.P_AGOSTO2007,NULL,DECODE(CA.P_SEPTIEMBRE2007,NULL,DECODE(CA.P_OCTUBRE2007,NULL,DECODE(CA.P_NOVIEMBRE2007,NULL,DECODE(CA.P_DICEMBRE2007,NULL,0,24),23),22),21),20),19),18),17),16),15),14),13),12),11),10),9),8),7),6),5),4),3),2),1 ) AS F_O, O.ID_FECHA AS F_V
FROM IMMUEBLES I, CATALOGO CA, OFERTAS O
WHERE CA.CODIGO_IMMUEBLE=I.CODIGO AND
I.CODIGO=O.CODIGO_IMMUEBLE


-- ESTARÍA CHULO QUE AÑADIERAS ESTA CONSULTA. Indica la fecha de venta y caracterísicas de los immuebles.

SELECT CM.NOMBRE ||' - '|| P.NOMBRE ||' - '|| C.NOMBRE AS LUGAR, I.CODIGO, T.NOMBRE AS TIPO, I.PRECIO_INICIO AS "PRECIO INICIAL", O.PRECIO_VENTA AS "PRECIO FINAL", F.MES ||' de '|| F.ANIO AS "FECHA DE VENTA"
FROM IMMUEBLES I, OFERTAS O, COMARCAS C, PROVINCIAS P, COMUNIDADES CM, TIPOS T, FECHAS F
WHERE O.CODIGO_IMMUEBLE = I.CODIGO AND 
O.VENDIDO=1 AND
I.ID_COMARCA=C.ID AND
C.ID_PROVINCIA=P.ID AND
P.ID_COMUNIDAD=CM.ID AND 
T.ID=I.ID_TIPO AND
O.ID_FECHA=F.ID



--EJERCICIO 9---------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- NUMERO DE IMMUEBLES EXISTENTES Y VENDIDOS POR ZONA

SELECT CM.NOMBRE||' - '|| P.NOMBRE ||' - '|| C.NOMBRE AS LUGAR, N.NUM AS "IMMUEBLES EXISTENTES", M.NUM AS "IMMUEBLES VENDIDOS" 
FROM (SELECT I.ID_COMARCA, COUNT(*) AS NUM
FROM IMMUEBLES I, OFERTAS O
WHERE O.CODIGO_IMMUEBLE = I.CODIGO AND
O.VENDIDO=1
GROUP BY I.ID_COMARCA) M,
(SELECT I.ID_COMARCA, COUNT(*) AS NUM
FROM IMMUEBLES I
GROUP BY I.ID_COMARCA) N,
COMARCAS C, PROVINCIAS P, COMUNIDADES CM
WHERE M.ID_COMARCA=C.ID AND
N.ID_COMARCA=C.ID AND
P.ID=C.ID_PROVINCIA AND
CM.ID=P.ID_COMUNIDAD
ORDER BY CM.NOMBRE, P.NOMBRE, C.NOMBRE ASC

-- NUMERO DE IMMUEBLES EXISTENTES Y VENDIDOS POR TIPO

SELECT T.NOMBRE, N.NUM AS "IMMUEBLES EXISTENTES", M.NUM AS "IMMUEBLES VENDIDOS" 
FROM (SELECT I.ID_TIPO, COUNT(*) AS NUM
FROM IMMUEBLES I, OFERTAS O
WHERE O.CODIGO_IMMUEBLE = I.CODIGO AND
O.VENDIDO=1
GROUP BY I.ID_TIPO) M,
(SELECT I.ID_TIPO, COUNT(*) AS NUM
FROM IMMUEBLES I
GROUP BY I.ID_TIPO) N,
TIPOS T
WHERE M.ID_TIPO=T.ID AND
N.ID_TIPO=T.ID