/* @BD_Vuelta_consultas.sql */
SET PAGESIZE 80
SET LINESIZE 132
/* 1a
   dorsal, nombre y equipo del corredor que ha ganado más etapas
*/
SELECT dorsal, nombre, idEquipo
  FROM Corredor
  WHERE dorsal IN (SELECT dorsal FROM Llegada WHERE ordenMeta = 1
                   GROUP BY dorsal
                   HAVING count(*) = (SELECT max(count(*)) FROM Llegada
                                       WHERE ordenMeta = 1
                                       GROUP BY dorsal));

/* 1b (si se añade el atributo con etapas ganadas
   dorsal, nombre y equipo del corredor que ha ganado más etapas
*/
SELECT dorsal, nombre, idEquipo
  FROM Corredor
  WHERE etapasG = (SELECT max(etapasG) FROM Corredor);

/* insertar tuplas para probar */
INSERT INTO Coronar VALUES (3, 43, 152, 99, 1);
INSERT INTO Coronar VALUES (8, 52, 152, 99, 1);
INSERT INTO Coronar VALUES (14, 36, 152, 99, 1);
INSERT INTO Coronar VALUES (15, 42, 152, 99, 1);
INSERT INTO Coronar VALUES (16, 37, 152, 99, 1);
INSERT INTO Coronar VALUES (20, 22, 152, 99, 1);

/* 2
   corredores que han puntuado en todos los puertos de primera categoría
*/
SELECT dorsal, nombre FROM Corredor D
WHERE NOT EXISTS (
        SELECT * FROM Puerto P WHERE categoria = 1
          AND NOT EXISTS (SELECT * FROM Coronar C, Hito H
                          WHERE C.idEtapa = H.idEtapa AND C.idHito = H.idHito
                            AND idPuerto = P.idPuerto AND dorsal = D.dorsal));

-- añadir un puerto de primera categoría que no pertenece a ningún hito
INSERT INTO Puerto VALUES (99, 'Puerto de prueba', 1, 1840);
INSERT INTO Puerto VALUES (98, 'Puerto ficticio', 1, 0);
INSERT INTO Hito   VALUES (21, 34, 'P', 0, 0.0, 'puerto ficticio', 98);

/* 2
   corredores que han puntuado en todos los puertos de primera categoría
   FALLA si hay algún puerto de primera categoría que no es un hito
*/
SELECT dorsal, nombre FROM Corredor D
WHERE NOT EXISTS (
        SELECT * FROM Puerto P WHERE categoria = 1
          AND NOT EXISTS (SELECT * FROM Coronar C, Hito H
                          WHERE C.idEtapa = H.idEtapa AND C.idHito = H.idHito
                            AND idPuerto = P.idPuerto AND dorsal = D.dorsal));

/* 2
   corredores que han puntuado en todos los puertos de primera categoría
   FALLA si hay algún puerto de primera categoría que no ha coronado nadie
*/
SELECT dorsal, nombre FROM Corredor D
WHERE NOT EXISTS (
        SELECT * FROM Hito H, Puerto P WHERE H.idPuerto = P.idPuerto AND categoria = 1
          AND NOT EXISTS (SELECT * FROM Coronar C, Hito H
                          WHERE C.idEtapa = H.idEtapa AND C.idHito = H.idHito
                            AND idPuerto = P.idPuerto AND dorsal = D.dorsal));

/* 2 CORREGIDA 
   corredores que han puntuado en todos los puertos de primera categoría
*/
SELECT dorsal, nombre FROM Corredor D
WHERE NOT EXISTS (
 SELECT * FROM Coronar C, Hito H, Puerto P
  WHERE C.idEtapa = H.idEtapa AND C.idHito = H.idHito AND H.idPuerto = P.idPuerto AND categoria = 1
    AND NOT EXISTS (SELECT * FROM Coronar C, Hito H
                     WHERE C.idEtapa = H.idEtapa AND C.idHito = H.idHito
                       AND idPuerto = P.idPuerto AND dorsal = D.dorsal));

/* 2b
   corredores que han puntuado en todos los puertos de primera categoría
   FALLA si hay algún puerto de primera categoría que no ha coronado nadie
*/
SELECT dorsal, nombre FROM Corredor D
WHERE NOT EXISTS (
        SELECT idEtapa, idHito FROM Hito H, Puerto P
         WHERE H.idPuerto = P.idPuerto AND categoria = 1
        MINUS
        SELECT idEtapa, idHito FROM Coronar WHERE dorsal = D.dorsal);

/* 2b CORREGIDA
   corredores que han puntuado en todos los puertos de primera categoría
*/
SELECT dorsal, nombre FROM Corredor D
WHERE NOT EXISTS (
 SELECT H.idEtapa, H.idHito FROM Coronar C, Hito H, Puerto P
  WHERE C.idEtapa = H.idEtapa AND C.idHito = H.idHito AND H.idPuerto = P.idPuerto AND categoria = 1
 MINUS
 SELECT idEtapa, idHito FROM Coronar WHERE dorsal = D.dorsal);

/* vista con corredores que han puntuado en algun puerto de primera categoría
*/
CREATE VIEW puntuan_puerto_cat1 AS (
  SELECT DISTINCT dorsal, H.idPuerto
   FROM Coronar C, Hito H, Puerto P
   WHERE C.idEtapa = H.idEtapa AND C.idHito = H.idHito AND H.idPuerto = P.idPuerto AND categoria = 1
);

/* 2c
   corredores que han puntuado en todos los puertos de primera categoría
*/
SELECT dorsal, nombre FROM Corredor D
WHERE NOT EXISTS (
        SELECT idPuerto FROM puntuan_puerto_cat1
        MINUS
        SELECT idPuerto FROM puntuan_puerto_cat1 WHERE dorsal = D.dorsal);

/* 2d
   corredores que han puntuado en todos los puertos de primera categoría
*/
SELECT dorsal, nombre FROM Corredor D
WHERE (SELECT count(*) FROM puntuan_puerto_cat1 WHERE dorsal = D.dorsal)
     =(SELECT count(distinct idPuerto) FROM puntuan_puerto_cat1);
		
/* eliminar los elementos añadidos para la prueba */
DROP VIEW puntuan_puerto_cat1;

DELETE FROM Coronar WHERE (idEtapa, idHito, dorsal) IN ((3, 43, 152));
DELETE FROM Coronar WHERE ordenPaso > 20;
DELETE FROM Hito WHERE idPuerto > 90;
DELETE FROM Puerto WHERE idPuerto > 90;