-- PRÁCTICA 2.- ALOJAMIENTOS RURALES
   -- Alumno: Antonio Rosas Diaconu
-- 1.- CREACIÓN DE LA BASE DE DATOS
   

-- ALOJAMIENTO: el nombre es obligatorio y no se repite.
CREATE TABLE ALOJAMIENTO (
    NOMBRE      VARCHAR2(40)
                CONSTRAINT ALOJAMIENTO_NOMBRE_PK PRIMARY KEY,
    DIRECCION   VARCHAR2(80),
    TELEFONO    VARCHAR2(12),
    NUM_HABS    NUMBER(2)
                CONSTRAINT ALOJAMIENTO_NUM_HABS_CK CHECK (NUM_HABS BETWEEN 1 AND 20)
);

-- ACTIVIDADES: código de 3 letras + guión bajo + 3 números.
CREATE TABLE ACTIVIDADES (
    CODIGO      CHAR(7)
                CONSTRAINT ACTIVIDADES_CODIGO_PK PRIMARY KEY
                CONSTRAINT ACTIVIDADES_CODIGO_CK CHECK (SUBSTR(CODIGO,4,1) = '_'),
    NOMBRE      VARCHAR2(40)
                CONSTRAINT ACTIVIDADES_NOMBRE_NN NOT NULL,
    DESCRIPCION VARCHAR2(100),
    NIVEL       VARCHAR2(15)
                CONSTRAINT ACTIVIDADES_NIVEL_CK CHECK (NIVEL IN ('Fácil','Medio','Avanzado'))
);

-- PERSONAL: IdPersonal autonumérico y nombre obligatorio
CREATE TABLE PERSONAL (
    IDPERSONAL  NUMBER(4) GENERATED ALWAYS AS IDENTITY
                CONSTRAINT PERSONAL_IDPERSONAL_PK PRIMARY KEY,
    NOMBRE      VARCHAR2(40)
                CONSTRAINT PERSONAL_NOMBRE_NN NOT NULL,
    DIRECCION   VARCHAR2(80),
    NIF         VARCHAR2(10),
    ALOJAMIENTO VARCHAR2(40)
                CONSTRAINT PERSONAL_ALOJAMIENTO_FK REFERENCES ALOJAMIENTO(NOMBRE)
);

-- HABITACION: el mismo número de habitación puede estar en varios alojamientos.
CREATE TABLE HABITACION (
    NUM_HABITACION NUMBER(3),
    ALOJAMIENTO    VARCHAR2(40)
                   CONSTRAINT HABITACION_ALOJAMIENTO_FK REFERENCES ALOJAMIENTO(NOMBRE),
    TIPO           VARCHAR2(15)
                   CONSTRAINT HABITACION_TIPO_CK CHECK (TIPO IN ('Estándar','Junior','Suite')),
    BANO           VARCHAR2(2) DEFAULT 'Si'
                   CONSTRAINT HABITACION_BANO_CK CHECK (BANO IN ('Si','No')),
    PRECIOHAB      NUMBER(6,2)
                   CONSTRAINT HABITACION_PRECIOHAB_CK CHECK (PRECIOHAB BETWEEN 20 AND 60
                                                          OR PRECIOHAB BETWEEN 100 AND 120),
    CONSTRAINT HABITACION_PK PRIMARY KEY (NUM_HABITACION, ALOJAMIENTO)
);

CREATE TABLE ACT_REALIZADA (
    NOMBRE_ALOJAMIENTO VARCHAR2(40)
                       CONSTRAINT ACT_REALIZADA_ALOJ_FK REFERENCES ALOJAMIENTO(NOMBRE),
    COD_ACTIVIDAD      CHAR(7)
                       CONSTRAINT ACT_REALIZADA_ACT_FK REFERENCES ACTIVIDADES(CODIGO),
    DIA_SEMANA         VARCHAR2(12) DEFAULT 'Lunes'
                       CONSTRAINT ACT_REALIZADA_DIA_SEMANA_CK
                       CHECK (DIA_SEMANA IN ('Lunes','Martes','Miércoles','Jueves','Viernes','Sábado','Domingo')),
    CONSTRAINT ACT_REALIZADA_PK PRIMARY KEY (NOMBRE_ALOJAMIENTO, COD_ACTIVIDAD)
);


-- 2.- MODIFICACIÓN DE LA BASE DE DATOS.

-- Nuevo campo Categoría en ALOJAMIENTO.
ALTER TABLE ALOJAMIENTO
    ADD (CATEGORIA VARCHAR2(10)
         CONSTRAINT ALOJAMIENTO_CATEGORIA_CK CHECK (CATEGORIA IN ('Superior','Primera','Segunda','Hostal')));

-- Nueva restricción del número de habitaciones: de 1 a 50,
--     sin las habitaciones 2, 6, 8 y de la 10 a la 15.
ALTER TABLE ALOJAMIENTO DROP CONSTRAINT ALOJAMIENTO_NUM_HABS_CK;

ALTER TABLE ALOJAMIENTO
    ADD CONSTRAINT ALOJAMIENTO_NUM_HABS_CK CHECK (NUM_HABS BETWEEN 1 AND 50
                                                  AND NUM_HABS NOT IN (2,6,8)
                                                  AND NUM_HABS NOT BETWEEN 10 AND 15);

-- En PERSONAL se elimina Dirección y se añade Puesto.
ALTER TABLE PERSONAL DROP COLUMN DIRECCION;

ALTER TABLE PERSONAL
    ADD (PUESTO VARCHAR2(15)
         CONSTRAINT PERSONAL_PUESTO_CK CHECK (PUESTO IN ('Recepcionista','Gerente','Director','Asistente')));

ALTER TABLE HABITACION DROP CONSTRAINT HABITACION_TIPO_CK;

ALTER TABLE HABITACION
    ADD CONSTRAINT HABITACION_TIPO_CK CHECK (TIPO IN ('Estándar','Junior','Suite','Master','Panorámica'));

-- Nuevo rango de precios: de 25 a 100 y de 150 a 500.
ALTER TABLE HABITACION DROP CONSTRAINT HABITACION_PRECIOHAB_CK;

ALTER TABLE HABITACION
    ADD CONSTRAINT HABITACION_PRECIOHAB_CK CHECK (PRECIOHAB BETWEEN 25 AND 100
                                                  OR PRECIOHAB BETWEEN 150 AND 500);

ALTER TABLE ACT_REALIZADA MODIFY (DIA_SEMANA DEFAULT NULL);

ALTER TABLE ACT_REALIZADA DROP CONSTRAINT ACT_REALIZADA_DIA_SEMANA_CK;

ALTER TABLE ACT_REALIZADA
    ADD CONSTRAINT ACT_REALIZADA_DIA_SEMANA_CK
    CHECK (DIA_SEMANA IN ('Lunes','Martes','Miércoles','Jueves','Viernes','Sábado'));

ALTER TABLE ACTIVIDADES DROP CONSTRAINT ACTIVIDADES_NIVEL_CK;

ALTER TABLE ACTIVIDADES
    ADD CONSTRAINT ACTIVIDADES_NIVEL_CK CHECK (NIVEL IN ('Fácil','Medio','Avanzado','Sin Determinar'));

-- Precio e IVA en ACTIVIDADES y columna virtual con el precio con IVA.
ALTER TABLE ACTIVIDADES ADD (PRECIO NUMBER(8,2));

ALTER TABLE ACTIVIDADES ADD (IVA NUMBER(4,2));

ALTER TABLE ACTIVIDADES
    ADD (PRECIO_CON_IVA NUMBER(8,2) AS (PRECIO + ROUND(PRECIO * IVA / 100, 2)));


-- 3.- INSERCIÓN DE DATOS EN LA BASE DE DATOS.

-- ALOJAMIENTO
INSERT INTO ALOJAMIENTO (NOMBRE, DIRECCION, TELEFONO, NUM_HABS, CATEGORIA)
    VALUES ('Cabañas Del Monte', 'Alameda El pinar - Jerte', '927-660-320', 4, 'Segunda');
INSERT INTO ALOJAMIENTO (NOMBRE, DIRECCION, TELEFONO, NUM_HABS, CATEGORIA)
    VALUES ('Hostal Benítez', 'C\ Bosque, nº 23 - Cabezuela del Valle', '927-850-632', 3, 'Primera');
INSERT INTO ALOJAMIENTO (NOMBRE, DIRECCION, TELEFONO, NUM_HABS, CATEGORIA)
    VALUES ('Bungalows La Jerteña', 'Ctra. Plasencia s/n - Jerte', '690-366-540', 3, 'Segunda');
INSERT INTO ALOJAMIENTO (NOMBRE, DIRECCION, TELEFONO, NUM_HABS, CATEGORIA)
    VALUES ('Hotel Gran Capitán', 'C\ De la Corte, nº 23 – Navaconcejo', '927-001-002', 5, 'Superior');
INSERT INTO ALOJAMIENTO (NOMBRE, DIRECCION, TELEFONO, NUM_HABS, CATEGORIA)
    VALUES ('Pensión Las Cerezas', 'Avda. de Cáceres, nº 20 – Tornavacas', '603-210-245', 3, 'Hostal');
INSERT INTO ALOJAMIENTO (NOMBRE, DIRECCION, TELEFONO, NUM_HABS, CATEGORIA)
    VALUES ('Hostal Verdolaga', 'C\ San Sebastián, nº 66 - Jerte', '699-996-688', 4, 'Superior');

-- PERSONAL
INSERT INTO PERSONAL (NOMBRE, NIF, ALOJAMIENTO, PUESTO)
    VALUES ('Antonio Ruiz', '22623150-G', 'Cabañas Del Monte', 'Gerente');
INSERT INTO PERSONAL (NOMBRE, NIF, ALOJAMIENTO, PUESTO)
    VALUES ('Marcos Domínguez', '08964550-Y', 'Hostal Benítez', 'Recepcionista');
INSERT INTO PERSONAL (NOMBRE, NIF, ALOJAMIENTO, PUESTO)
    VALUES ('María Cortés', '53369123-B', 'Bungalows La Jerteña', 'Director');
INSERT INTO PERSONAL (NOMBRE, NIF, ALOJAMIENTO, PUESTO)
    VALUES ('Valentín Masana', '55669712-Q', 'Hotel Gran Capitán', 'Asistente');
INSERT INTO PERSONAL (NOMBRE, NIF, ALOJAMIENTO, PUESTO)
    VALUES ('Silvia Martín', '50036921-W', 'Pensión Las Cerezas', 'Gerente');
INSERT INTO PERSONAL (NOMBRE, NIF, ALOJAMIENTO, PUESTO)
    VALUES ('Pedro López', '03621788-N', 'Hostal Verdolaga', 'Director');

-- HABITACION
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Cabañas Del Monte', 10, 'Junior', 'Si', 30);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Cabañas Del Monte', 11, 'Junior', 'Si', 30);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Cabañas Del Monte', 12, 'Junior', 'No', 30);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Cabañas Del Monte', 13, 'Junior', 'No', 30);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Hostal Benítez', 20, 'Estándar', 'No', 75);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Hostal Benítez', 21, 'Estándar', 'No', 75);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Hostal Benítez', 22, 'Suite', 'Si', 200);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Bungalows La Jerteña', 35, 'Estándar', 'No', 25);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Bungalows La Jerteña', 36, 'Suite', 'Si', 65);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Bungalows La Jerteña', 37, 'Suite', 'Si', 65);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Hotel Gran Capitán', 40, 'Panorámica', 'Si', 300);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Hotel Gran Capitán', 41, 'Panorámica', 'Si', 300);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Pensión Las Cerezas', 50, 'Master', 'Si', 55);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Pensión Las Cerezas', 51, 'Master', 'Si', 55);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Pensión Las Cerezas', 52, 'Junior', 'No', 35);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Hostal Verdolaga', 100, 'Junior', 'Si', 45);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Hostal Verdolaga', 101, 'Junior', 'Si', 45);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Hostal Verdolaga', 102, 'Estándar', 'No', 25);
INSERT INTO HABITACION (ALOJAMIENTO, NUM_HABITACION, TIPO, BANO, PRECIOHAB) VALUES ('Hostal Verdolaga', 103, 'Estándar', 'No', 25);

-- ACTIVIDADES
INSERT INTO ACTIVIDADES (CODIGO, NOMBRE, DESCRIPCION, NIVEL, PRECIO, IVA)
    VALUES ('BCL_001', 'Excursión en bicicleta', 'Visita parajes del entorno', 'Medio', 50.20, 10);
INSERT INTO ACTIVIDADES (CODIGO, NOMBRE, DESCRIPCION, NIVEL, PRECIO, IVA)
    VALUES ('ESC_001', 'Escalada', 'Vista panorámicas excelentes', 'Avanzado', 26.50, 10);
INSERT INTO ACTIVIDADES (CODIGO, NOMBRE, DESCRIPCION, NIVEL, PRECIO, IVA)
    VALUES ('SND_001', 'Rutas de Senderismo', 'Gargantas y piscinas naturales', 'Fácil', 15.20, 21);
INSERT INTO ACTIVIDADES (CODIGO, NOMBRE, DESCRIPCION, NIVEL, PRECIO, IVA)
    VALUES ('TRL_001', 'Bajada en tirolina', 'Desde el Alto del Ángel hasta la piscina natural El descanso', 'Sin Determinar', 40.20, 21);
INSERT INTO ACTIVIDADES (CODIGO, NOMBRE, DESCRIPCION, PRECIO, IVA)
    VALUES ('VST_001', 'Visitas al Museo del Pimiento', 'Conocer cómo se elabora el famoso pimentón de la vera', 6.50, 10);

-- ACT_REALIZADA
INSERT INTO ACT_REALIZADA (NOMBRE_ALOJAMIENTO, COD_ACTIVIDAD, DIA_SEMANA) VALUES ('Cabañas Del Monte', 'BCL_001', 'Martes');
INSERT INTO ACT_REALIZADA (NOMBRE_ALOJAMIENTO, COD_ACTIVIDAD, DIA_SEMANA) VALUES ('Cabañas Del Monte', 'ESC_001', 'Jueves');
INSERT INTO ACT_REALIZADA (NOMBRE_ALOJAMIENTO, COD_ACTIVIDAD, DIA_SEMANA) VALUES ('Hostal Benítez', 'SND_001', 'Sábado');
INSERT INTO ACT_REALIZADA (NOMBRE_ALOJAMIENTO, COD_ACTIVIDAD, DIA_SEMANA) VALUES ('Bungalows La Jerteña', 'TRL_001', 'Miércoles');
INSERT INTO ACT_REALIZADA (NOMBRE_ALOJAMIENTO, COD_ACTIVIDAD, DIA_SEMANA) VALUES ('Bungalows La Jerteña', 'BCL_001', 'Lunes');
INSERT INTO ACT_REALIZADA (NOMBRE_ALOJAMIENTO, COD_ACTIVIDAD, DIA_SEMANA) VALUES ('Hotel Gran Capitán', 'VST_001', 'Martes');
INSERT INTO ACT_REALIZADA (NOMBRE_ALOJAMIENTO, COD_ACTIVIDAD, DIA_SEMANA) VALUES ('Hotel Gran Capitán', 'SND_001', 'Jueves');
INSERT INTO ACT_REALIZADA (NOMBRE_ALOJAMIENTO, COD_ACTIVIDAD, DIA_SEMANA) VALUES ('Pensión Las Cerezas', 'TRL_001', 'Viernes');
INSERT INTO ACT_REALIZADA (NOMBRE_ALOJAMIENTO, COD_ACTIVIDAD, DIA_SEMANA) VALUES ('Pensión Las Cerezas', 'SND_001', 'Sábado');
INSERT INTO ACT_REALIZADA (NOMBRE_ALOJAMIENTO, COD_ACTIVIDAD, DIA_SEMANA) VALUES ('Hostal Verdolaga', 'VST_001', 'Miércoles');
INSERT INTO ACT_REALIZADA (NOMBRE_ALOJAMIENTO, COD_ACTIVIDAD, DIA_SEMANA) VALUES ('Hostal Verdolaga', 'ESC_001', 'Sábado');

COMMIT;



-- 4.- MODIFICACIONES DE DATOS EN LA BASE DE DATOS.

-- Subida de precios en habitaciones Junior o Estándar.
UPDATE HABITACION
SET PRECIOHAB = PRECIOHAB + 15
WHERE BANO = 'Si'
AND TIPO IN ('Junior','Estándar');

UPDATE HABITACION
SET PRECIOHAB = PRECIOHAB + 5
WHERE BANO = 'No'
AND TIPO IN ('Junior','Estándar');

-- Precio e IVA de las actividades según su nivel.
UPDATE ACTIVIDADES SET PRECIO = 20, IVA = 21 WHERE NIVEL = 'Medio';

UPDATE ACTIVIDADES SET PRECIO = 10, IVA = 21 WHERE NIVEL = 'Fácil';

UPDATE ACTIVIDADES SET PRECIO = 30, IVA = 10 WHERE NIVEL = 'Avanzado';

UPDATE ACTIVIDADES SET PRECIO = 0, IVA = 0 WHERE NIVEL = 'Sin Determinar' OR NIVEL IS NULL;

-- Dejar las rutas de senderismo y pasar a las excursiones en bicicleta
UPDATE ACT_REALIZADA
SET COD_ACTIVIDAD = 'BCL_001'
WHERE NOMBRE_ALOJAMIENTO = 'Hostal Benítez'
AND COD_ACTIVIDAD = 'SND_001';

-- Los alojamientos con más de 4 habitaciones que tengan habitaciones Estándar o Junior pasan a ser regentados por Marcos Domínguez.
UPDATE PERSONAL
SET NOMBRE = 'Marcos Domínguez', NIF = '08964550-Y'
WHERE ALOJAMIENTO IN (SELECT A.NOMBRE
                      FROM ALOJAMIENTO A
                      WHERE A.NUM_HABS > 4
                      AND A.NOMBRE IN (SELECT H.ALOJAMIENTO
                                       FROM HABITACION H
                                       WHERE H.TIPO IN ('Estándar','Junior')));

-- Se suprime la visita al museo del pimiento.
UPDATE ACT_REALIZADA
SET COD_ACTIVIDAD = 'TRL_001'
WHERE COD_ACTIVIDAD = 'VST_001';

DELETE FROM ACTIVIDADES WHERE CODIGO = 'VST_001';

COMMIT;


-- 5.- CONSULTAS ACERCA DE LA INFORMACIÓN EN LA BASE DE DATOS

-- Cuántos alojamientos de los que gestiona el gerente apellidado Ruiz ofrecen actividades de dificultad media.
SELECT COUNT(*) AS NUM_ALOJAMIENTOS
FROM ALOJAMIENTO A, PERSONAL P
WHERE A.NOMBRE = P.ALOJAMIENTO
AND P.PUESTO = 'Gerente'
AND P.NOMBRE LIKE '%Ruiz%'
AND A.NOMBRE IN (SELECT AR.NOMBRE_ALOJAMIENTO
                 FROM ACT_REALIZADA AR, ACTIVIDADES AC
                 WHERE AR.COD_ACTIVIDAD = AC.CODIGO
                 AND AC.NIVEL = 'Medio');

-- Costo total del alquiler entre todas las habitaciones sin baño de los alojamientos situados en Jerte o Cabezuela del Valle.
SELECT SUM(H.PRECIOHAB) AS TOTAL_HABITACIONES
FROM HABITACION H, ALOJAMIENTO A
WHERE H.ALOJAMIENTO = A.NOMBRE
AND H.BANO = 'No'
AND (A.DIRECCION LIKE '%Jerte%' OR A.DIRECCION LIKE '%Cabezuela del Valle%');

-- Alojamiento con el menor coste entre sus habitaciones, siempre que tenga más de 4 habitaciones.
SELECT H.ALOJAMIENTO, MIN(H.PRECIOHAB) AS PRECIO_MINIMO
FROM HABITACION H, ALOJAMIENTO A
WHERE H.ALOJAMIENTO = A.NOMBRE
AND A.NUM_HABS > 4
GROUP BY H.ALOJAMIENTO
HAVING MIN(H.PRECIOHAB) <= ALL (SELECT MIN(H2.PRECIOHAB)
                                FROM HABITACION H2, ALOJAMIENTO A2
                                WHERE H2.ALOJAMIENTO = A2.NOMBRE
                                AND A2.NUM_HABS > 4
                                GROUP BY H2.ALOJAMIENTO);

-- Alojamiento cuya media de precio de todas sus habitaciones es la más barata
SELECT ALOJAMIENTO, AVG(PRECIOHAB) AS PRECIO_MEDIO
FROM HABITACION
GROUP BY ALOJAMIENTO
HAVING AVG(PRECIOHAB) <= ALL (SELECT AVG(PRECIOHAB)
                              FROM HABITACION
                              GROUP BY ALOJAMIENTO);

-- Teléfono, nombre del alojamiento, nombre del responsable y nombre de la actividad de los alojamientos que ofrecen menos de 2 actividades
SELECT A.TELEFONO, A.NOMBRE AS ALOJAMIENTO, P.NOMBRE AS RESPONSABLE, AC.NOMBRE AS ACTIVIDAD
FROM ALOJAMIENTO A, PERSONAL P, ACT_REALIZADA AR, ACTIVIDADES AC
WHERE A.NOMBRE = P.ALOJAMIENTO
AND A.NOMBRE = AR.NOMBRE_ALOJAMIENTO
AND AR.COD_ACTIVIDAD = AC.CODIGO
AND A.NOMBRE IN (SELECT NOMBRE_ALOJAMIENTO
                 FROM ACT_REALIZADA
                 GROUP BY NOMBRE_ALOJAMIENTO
                 HAVING COUNT(*) < 2);


 -- 6.- CONSULTAS AVANZADAS

-- Alojamiento con el menor coste entre sus habitaciones, siempre que tenga más de 4 habitaciones.
SELECT H.ALOJAMIENTO, MIN(H.PRECIOHAB) AS PRECIO_MINIMO
FROM HABITACION H, ALOJAMIENTO A
WHERE H.ALOJAMIENTO = A.NOMBRE
AND A.NUM_HABS > 4
GROUP BY H.ALOJAMIENTO
HAVING MIN(H.PRECIOHAB) <= ALL (SELECT MIN(H2.PRECIOHAB)
                                FROM HABITACION H2, ALOJAMIENTO A2
                                WHERE H2.ALOJAMIENTO = A2.NOMBRE
                                AND A2.NUM_HABS > 4
                                GROUP BY H2.ALOJAMIENTO);

-- Alojamiento cuya media de todas sus habitaciones es la más barata.
SELECT ALOJAMIENTO, AVG(PRECIOHAB) AS PRECIO_MEDIO
FROM HABITACION
GROUP BY ALOJAMIENTO
HAVING AVG(PRECIOHAB) <= ALL (SELECT AVG(PRECIOHAB)
                              FROM HABITACION
                              GROUP BY ALOJAMIENTO);

-- Cuántos alojamientos ofrecen cada actividad
SELECT AC.CODIGO, AC.NOMBRE, COUNT(*) AS NUM_ALOJAMIENTOS
FROM ACTIVIDADES AC, ACT_REALIZADA AR
WHERE AC.CODIGO = AR.COD_ACTIVIDAD
GROUP BY AC.CODIGO, AC.NOMBRE;

-- Número de actividades que se realizan cada día de la semana sin contar los findes.
SELECT DIA_SEMANA, COUNT(*) AS NUM_ACTIVIDADES
FROM ACT_REALIZADA
WHERE DIA_SEMANA NOT IN ('Sábado','Domingo')
GROUP BY DIA_SEMANA;

-- Precio total de las actividades según su nivel, de mayor a menor, para los alojamientos de categoría Primera y Segunda
SELECT AC.NIVEL, SUM(AC.PRECIO) AS TOTAL_SIN_IVA, SUM(AC.PRECIO_CON_IVA) AS TOTAL_CON_IVA
FROM ALOJAMIENTO A, ACT_REALIZADA AR, ACTIVIDADES AC
WHERE A.NOMBRE = AR.NOMBRE_ALOJAMIENTO
AND AR.COD_ACTIVIDAD = AC.CODIGO
AND A.CATEGORIA IN ('Primera','Segunda')
GROUP BY AC.NIVEL
ORDER BY TOTAL_SIN_IVA DESC;