-- Ejercicio 1)

CREATE DATABASE IF NOT EXISTS `TP3`;

USE `TP3`;

DROP TABLE IF EXISTS `Autor`;
DROP TABLE IF EXISTS `Libro`;
DROP TABLE IF EXISTS `Escribe`;

CREATE TABLE `Autor` (
  `ID` INT AUTO_INCREMENT,
  `Nombre` VARCHAR(100) NOT NULL,
  `Apellido` VARCHAR(100) NOT NULL,
  `Nacionalidad` VARCHAR(100) NOT NULL,
  `Residencia` VARCHAR(100) NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Libro` (
  `ISBN` VARCHAR(20),
  `Titulo` VARCHAR(100) NOT NULL,
  `Editorial` VARCHAR(100) NOT NULL,
  `Precio` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`ISBN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Escribe` (
  `ReferenciaAutor` INT UNSIGNED NOT NULL REFERENCES `Autor`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  `ReferenciaLibro` VARCHAR(20) NOT NULL REFERENCES `Libro`(`ISBN`) ON DELETE CASCADE ON UPDATE CASCADE,
  `Año` INT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Ejercicio 2)

CREATE INDEX index_apellido ON `Autor`(`Apellido`);
CREATE INDEX index_titulo ON `Libro`(`Titulo`);

-- Ejercicio 3)

INSERT INTO `Autor`(`Nombre`, `Apellido`, `Nacionalidad`, `Residencia`) VALUES 
  ("Abelardo", "Castillo", "Argentino", "Rosario"),
  ("Miguel", "Lopez", "Boliviano", "La Paz"),
  ("Leandro", "Gonzalez", "Boliviano", "La Paz"),
  ("Mauricio", "Torres", "Argentino", "Buenos Aires");

INSERT INTO `Libro` VALUES
  (9788380082243, "Historia de la Argentina IV", "UNR", 14999),
  (9783961890262, "Algebra y Geometria I", "UNR", 25499),
  (9789380028408, "Un viaje al centro del tetraedro", "UNR", 16999),
  (9781537570716, "Parry Hotter", "Mandragora", 18999),
  (9788427213548, "Goblins y otros monstruos", "Mandragora", 13499);

INSERT INTO `Escribe` VALUES
  (1, 9788380082243, 1998),
  (4, 9783961890262, 1995),
  (4, 9789380028408, 2004),
  (2, 9781537570716, 2007),
  (3, 9788427213548, 1968);

-- Ejercicio 4)

-- a)
UPDATE `Autor` 
SET `Residencia` = "Buenos Aires" 
WHERE `Nombre` = "Abelardo" AND `Apellido` = "Castillo";

-- b)
UPDATE `Libro` 
SET `Precio` = `Precio` * 1.1 
WHERE `Editorial` = "UNR";

-- c)
UPDATE `Libro`
SET `Precio` = 
  CASE
    WHEN `Precio` < 20000 THEN `Precio` * 1.2
    ELSE `Precio` * 1.1
  END
WHERE `Libro`.`ISBN` IN (SELECT ReferenciaLibro 
                         FROM Autor 
                         JOIN Escribe 
                         ON Escribe.ReferenciaAutor = Autor.ID 
                         WHERE Nacionalidad <> "Argentino");

-- d)
DELETE `Libro` 
FROM `Libro` 
JOIN `Escribe` ON `Escribe`.`ReferenciaLibro` = `Libro`.`ISBN` 
WHERE `Año` = 1998;

-- Ejercicio 5)

USE `Inmobiliaria`;

-- a)
SELECT DISTINCT `nombre`, `apellido` 
FROM `PoseeInmueble` 
JOIN `Persona` ON `Persona`.`codigo` = `PoseeInmueble`.`codigo_propietario`;

-- b)
SELECT `codigo` 
FROM `Inmueble` 
WHERE precio >= 600000 AND precio <= 700000;

-- c)
SELECT nombre, apellido 
FROM Persona 
WHERE codigo NOT IN 
  (SELECT codigo 
   FROM PrefiereZona, Persona 
   WHERE codigo=codigo_cliente 
      AND (nombre_zona<>"Norte" 
           OR nombre_poblacion<>"Santa Fe"));

-- d)
SELECT DISTINCT vendedor 
FROM Cliente, PrefiereZona 
WHERE codigo=codigo_cliente 
AND nombre_poblacion="Rosario" 
AND nombre_zona="Centro"

-- e)
SELECT nombre, apellido 
FROM (SELECT * FROM Cliente 
      WHERE codigo IN (SELECT vendedor FROM Cliente)) AS T, Persona 
      WHERE Persona.codigo=T.vendedor;

-- f)
SELECT * 
FROM Persona AS PER 
WHERE NOT EXISTS( 
	SELECT * FROM Zona AS Z 
        WHERE nombre_poblacion="Rosario" AND 
        	NOT EXISTS(
			SELECT * FROM PrefiereZona as P 
			WHERE P.codigo_cliente=PER.codigo 
			AND P.nombre_poblacion=Z.nombre_poblacion 
			AND P.nombre_zona=Z.nombre_zona));

-- g)
