/* Borramos las tablas si ya existen */

DROP TABLE IF EXISTS Reservas;
DROP TABLE IF EXISTS Investigadores;
DROP TABLE IF EXISTS Equipos;
DROP TABLE IF EXISTS Facultad;

/* Creamos las tablas */

CREATE TABLE Facultad (
  IdFacultad int(11) NOT NULL,
  NomFacultad varchar(50) COLLATE utf8_spanish_ci,
  PRIMARY KEY (IdFacultad)
);


CREATE TABLE Equipos (
  IdEquipo varchar(4) NOT NULL,
  IdFacultad int(11),
  Descripcion varchar(50),
  PRIMARY KEY (IdEquipo),
  FOREIGN KEY (IdFacultad) REFERENCES Facultad(IdFacultad)
);


CREATE TABLE Investigadores (
  IdInvestigador int(11) NOT NULL,
  DNI varchar(10) COLLATE utf8_spanish_ci,
  NomInvestigador varchar(35) COLLATE utf8_spanish_ci,
  ApellInvestigador varchar(50) COLLATE utf8_spanish_ci,
  IdFacultad int(11),
  PRIMARY KEY (IdInvestigador),
  FOREIGN KEY (IdFacultad) REFERENCES Facultad(IdFacultad)
);

CREATE TABLE Reservas (
  IdReserva bigint(20) NOT NULL AUTO_INCREMENT,
  IdInvestigador int(11),
  IdEquipo varchar(4),
  FechaInicio datetime,
  FechaFin datetime,
  PRIMARY KEY (IdReserva),
  FOREIGN KEY (IdInvestigador) REFERENCES Investigadores(IdInvestigador),
  FOREIGN KEY (IdEquipo) REFERENCES Equipos(IdEquipo)
);

/* Insertamos los datos */

INSERT INTO Facultad (IdFacultad, NomFacultad) VALUES
(1, 'Ciencias Exactas'),
(2, 'Ciencias Naturales'),
(3, 'Ciencia y Tecnología'),
(4, 'Bioquímica y Ciencias Biológicas');

INSERT INTO Investigadores (IdInvestigador, DNI, NomInvestigador, ApellInvestigador, IdFacultad) VALUES
(1, '38486831X', 'ESTEFANIA', 'LÓPEZ DE PABLO GARCIA UCEDA', 1),
(2, '56234233K', 'QUERALT', 'ANGUERA VILAFRANCA', 1),
(3, '23435343P', 'JOAN', 'BASTARDES SOTO', 2),
(4, 'X3543098R', 'RAQUEL', 'RAYA GAVILAN', 3),
(5, '32544333I', 'ELIOT', 'BIDAULT CULLERÉS', 4),
(6, '37879998D', 'LLUÍS', 'VISO GILABERT', 4);

INSERT INTO Equipos (IdEquipo, IdFacultad, Descripcion) VALUES
('H235', 3, 'Multimetro digital FLUKE'),
('H503', 2, 'Telemetro laser SICK'),
('K231', 3, 'Lente para camara FUJINON-TV'),
('K456', 2, 'Anemómetro JORSEK'),
('M234', 3, 'Cámara digital SONY'),
('M342', 4, 'Fuente de Voltaje TEKTRONIX');

INSERT INTO Reservas (IdReserva, IdInvestigador, IdEquipo, FechaInicio, FechaFin) VALUES
(1, 1, 'H503', '2018-09-09 00:00:00', '2018-09-23 00:00:00'),
(2, 1, 'H235', '2018-09-09 00:00:00', '2018-12-14 00:00:00'),
(3, 5, 'M342', '2018-09-13 00:00:00', '2018-09-21 00:00:00'),
(4, 1, 'M234', '2018-09-16 00:00:00', '2018-10-01 00:00:00'),
(5, 2, 'K231', '2018-11-20 00:00:00', '2018-12-25 00:00:00'),
(6, 5, 'H503', '2018-11-01 00:00:00', '2018-10-12 00:00:00');
/*

Consultas

1. Mostrar los datos de los investigadores por orden de DNI
select *
from Investigadores
order by dni

2. Mostrar DNI y en otra columna conjuntamente el nombre y el apellido
(CONCAT) de todos los investigadores por orden alfabético de los apellidos.
select dni, concat_ws(' ',nomInvestigador,ApellInvestigador) as Investigador
from Investigadores
order by ApellInvestigador


3. Mostrar DNI, nombre y apellidos de los investigadores y el nombre de la
facultad ordenados por facultad
SELECT dni, NomInvestigador, ApellInvestigador, NomFacultad
from Investigadores
	join Facultad on Facultad.IdFacultad = Investigadores.IdFacultad
order by nomFacultad


4. Mostrar todas las facultades que contengan la palabra ciencias en su
nombre
select *
from Facultad
where nomfacultad LIKE '%ciencias%'


5. Mostrar todos los equipos y el nombre de la facultad a la que pertenece
select Equipos.*,Facultad.NomFacultad
from Equipos
	join Facultad on Facultad.IdFacultad = Equipos.IdFacultad;
    



6. Mostrar todos los equipos que pertenecen a la facultad de Ciencia y
tecnología
select Equipos.*,Facultad.NomFacultad
from Equipos
	join Facultad on Facultad.IdFacultad = Equipos.IdFacultad
WHERE Facultad.IdFacultad = 3;


7. Mostrar todos los equipos cuyo código empiezan por H
select *
FROM Equipos
where IdEquipo like 'H%';

8. Mostrar todos los investigadores de las facultades 2 y 4. Tiene que aparecer
también el nombre de la facultad
SELECT Investigadores.*, Facultad.NomFacultad
from Investigadores
	join Facultad on Facultad.IdFacultad = Investigadores.IdFacultad
where Investigadores.IdFacultad in (2,4);



9. Mostrar las reservas de noviembre. Es decir, se tienen que mostrar los
campos DNI, IdEquipo, FechaInicio donde Fecha inicio sea en noviembre.
SELECT Investigadores.DNI, Equipos.IdEquipo, Reservas.FechaInicio
from Reservas
 join Investigadores on Investigadores.IdInvestigador = Reservas.IdInvestigador
 join Equipos on Equipos.IdEquipo = Reservas.IdEquipo
where Reservas.FechaInicio BETWEEN '2018-11-01' and '2018-11-30';


10. Mostrar los equipos que estarán reservados entre noviembre y diciembre,
es decir que tanto su fecha de inicio como su fecha final estén entre
noviembre y diciembre.
SELECT Investigadores.DNI, Equipos.IdEquipo, Reservas.FechaInicio, Reservas.FechaFin
from Reservas
 join Investigadores on Investigadores.IdInvestigador = Reservas.IdInvestigador
 join Equipos on Equipos.IdEquipo = Reservas.IdEquipo
where Reservas.FechaInicio BETWEEN '2018-11-01' and '2018-12-31' 
or Reservas.FechaFin BETWEEN '2018-11-01' and '2018-12-31'


11. Mostrar NomInvestigador, ApellInvestigador, IdEquipo, Descripción,
FechaInicio y FechaFin de las reservas efectuadas. La consulta mostrará los
resultados ordenados por ApellInvestigador y FechaInicio

SELECT Investigadores.NomInvestigador, Investigadores.ApellInvestigador, Equipos.IdEquipo, Equipos.Descripcion, Reservas.FechaInicio, Reservas.FechaFin
from Reservas
 join Investigadores on Investigadores.IdInvestigador = Reservas.IdInvestigador
 join Equipos on Equipos.IdEquipo = Reservas.IdEquipo
order by Investigadores.ApellInvestigador, Reservas.FechaInicio



12. Mostrar el DNI y el nombre y apellidos de aquellos investigadores que han
hecho más de una reserva.

SELECT Investigadores.DNI, Investigadores.NomInvestigador, Investigadores.ApellInvestigador, count(Reservas.IdReserva)
from Reservas
 join Investigadores on Investigadores.IdInvestigador = Reservas.IdInvestigador
group by Investigadores.DNI, Investigadores.NomInvestigador, Investigadores.ApellInvestigador
having COUNT(Reservas.IdReserva) > 1


13. Mostrar los datos de los investigadores y el número de reservas que han
hecho.

SELECT Investigadores.DNI, Investigadores.NomInvestigador, Investigadores.ApellInvestigador, count(Reservas.IdReserva)
from Reservas
 right join Investigadores on Investigadores.IdInvestigador = Reservas.IdInvestigador
group by Investigadores.DNI, Investigadores.NomInvestigador, Investigadores.ApellInvestigador


14. Mostrar los datos de los investigadores que han hecho reservas de equipos
que no son de su facultad.
SELECT DISTINCT Investigadores.*
from Reservas
 join Investigadores on Investigadores.IdInvestigador = Reservas.IdInvestigador
 join Equipos on Equipos.IdEquipo = Reservas.IdEquipo
where Investigadores.IdFacultad <> Equipos.IdFacultad


15. Mostrar el nombre de las facultades que ningún investigador ha realizado
una reserva.

---- Miramos primero resultados previos para no liarnos -----
SELECT Facultad.*, Investigadores.*, Reservas.*
from Facultad
 left join Investigadores on Facultad.IdFacultad = Investigadores.IdFacultad
 left join Reservas on Reservas.IdInvestigador = Investigadores.IdInvestigador;
 _____________________________________________
SELECT Facultad.NomFacultad
from Facultad
 left join Investigadores on Facultad.IdFacultad = Investigadores.IdFacultad
 left join Reservas on Reservas.IdInvestigador = Investigadores.IdInvestigador
 where reservas.IdReserva is null
 
 _____________________________________


 SELECT Facultad.NomFacultad,count(Reservas.IdInvestigador)
from Facultad
 left join Investigadores on Facultad.IdFacultad = Investigadores.IdFacultad
 left join Reservas on Reservas.IdInvestigador = Investigadores.IdInvestigador
group by Facultad.NomFacultad
having count(Reservas.IdInvestigador) = 0

16. Mostrar el número de serie y el nombre de los equipos que nunca han sido
reservados.
SELECT Equipos.IdEquipo, Equipos.Descripcion
from Equipos
 left join Reservas on Reservas.IdEquipo = Equipos.IdEquipo
 where Reservas.IdReserva is null;

17. Mostrar por cada facultad, cuántos investigadores tiene y cuántos equipos
tiene.
SELECT Facultad.IdFacultad, Facultad.NomFacultad, count(distinct Investigadores.IdInvestigador), COUNT(distinct Equipos.IdEquipo)
from Facultad
 left join Investigadores on Investigadores.IdFacultad = Facultad.IdFacultad
 left JOIN Equipos on Equipos.IdFacultad = Facultad.IdFacultad
group by Facultad.IdFacultad, Facultad.NomFacultad;

----- Otra forma con subselects ----

SELECT Facultad.IdFacultad, 
Facultad.NomFacultad, 
(select count(Investigadores.IdInvestigador) from Investigadores where Investigadores.IdFacultad = Facultad.IdFacultad) as num_inv,
(select count(Equipos.IdEquipo) from Equipos where Equipos.IdFacultad = Facultad.IdFacultad) as num_equ
from Facultad;



18. Crear una vista con los datos del punto anterior
create or replace view v_facultad_n_inv_n_equ AS
(
SELECT Facultad.IdFacultad, Facultad.NomFacultad, count(distinct Investigadores.IdInvestigador) as n_inv, COUNT(distinct Equipos.IdEquipo) as n_equ
from Facultad
 left join Investigadores on Investigadores.IdFacultad = Facultad.IdFacultad
 left JOIN Equipos on Equipos.IdFacultad = Facultad.IdFacultad
group by Facultad.IdFacultad, Facultad.NomFacultad
)

19. Mostrar la facultad que tiene más de dos equipos.
SELECT V_facultad_n_inv_n_equ.IdFacultad, V_facultad_n_inv_n_equ.NomFacultad, V_facultad_n_inv_n_equ.n_equ
from V_facultad_n_inv_n_equ
where V_facultad_n_inv_n_equ.n_equ > 2


20. Ceder el equipo H235 a la facultad que no tiene ningún equipo.
------
NO FUNCIONA ASI
update equipos
set equipos.IdFacultad = 
(
    select facultad.IdFacultad
    from facultad
        left join equipos on equipos.IdFacultad = facultad.IdFacultad
    where equipos.IdEquipo is null
    )
where equipos.IdEquipo = 'H235';

_____________________________________
FORMA CORRECTA

select @idfac := Facultad.IdFacultad
				from Facultad
				where Facultad.IdFacultad not in (select DISTINCT Equipos.IdFacultad from Equipos);

UPDATE Equipos
set Equipos.IdFacultad = @idfac
where Equipos.IdEquipo = 'H235';



21. Mostrar el idFacultad, nombre de la facultad, el idEquipo, la
descripción y el número de reservas de cada equipo.
select Facultad.IdFacultad, Facultad.NomFacultad, Equipos.IdEquipo, Equipos.Descripcion,count(Reservas.IdReserva) as n_reserva
from Equipos
	left join Facultad on Facultad.IdFacultad = Equipos.IdFacultad
    left JOIN Reservas on Reservas.IdEquipo = Equipos.IdEquipo
group by Facultad.IdFacultad, Facultad.NomFacultad, Equipos.IdEquipo, Equipos.Descripcion


22. Crear una vista del punto anterior
create or replace view v_equipos_reservas as 
(
select Facultad.IdFacultad, Facultad.NomFacultad, Equipos.IdEquipo, Equipos.Descripcion,count(Reservas.IdReserva) as n_reserva
from Equipos
	left join Facultad on Facultad.IdFacultad = Equipos.IdFacultad
    left JOIN Reservas on Reservas.IdEquipo = Equipos.IdEquipo
group by Facultad.IdFacultad, Facultad.NomFacultad, Equipos.IdEquipo, Equipos.Descripcion
    )


23. Crear una vista por cada facultad que muestre los investigadores que
pertenecen a ella. (En total 4 vistas)
create or replace view v_investigadores_c_exactas AS
(
    select Investigadores.*
    	from Investigadores
    where Investigadores.IdFacultad=1
);

create or replace view v_investigadores_c_naturales AS
(
    select Investigadores.*
    	from Investigadores
    where Investigadores.IdFacultad=2
);
create or replace view v_investigadores_c_tecnologia AS
(
    select Investigadores.*
    	from Investigadores
    where Investigadores.IdFacultad=3
);
create or replace view v_investigadores_c_biologicas AS
(
    select Investigadores.*
    	from Investigadores
    where Investigadores.IdFacultad=4
);





24. Crear una vista que nos muestre todos los datos de las reservas
incluyendo, nombre y apellidos del investigador, nombre de la
facultad del investigador, descripción del equipo, nombre de la
facultad del equipo. (En este caso como la facultad puede ser diferente
la del investigador y la del equipo, será necesario hacer 2 joins con la
tabla facultad y poner alias para distinguirlas) (También se puede
hacer con subselects)
create or replace view v_reservas AS
(
select Reservas.*, Investigadores.NomInvestigador, Investigadores.ApellInvestigador, Investigadores.IdFacultad as idFacInv, fi.NomFacultad as NomFacInv,
	   Equipos.Descripcion, Equipos.IdFacultad as IdFacEq, fe.NomFacultad as NomFacEq
from Reservas
	left join Investigadores on Investigadores.IdInvestigador = Reservas.IdInvestigador
    	join Facultad as fi on Investigadores.IdFacultad = fi.IdFacultad
    join Equipos on Equipos.IdEquipo = Reservas.IdEquipo
		join Facultad as fe on Equipos.IdFacultad = fe.IdFacultad
)


25. Crear un Procedure que tenga un parámetro de entrada (idFac) y que nos
devuelva los investigadores filtrados por la facultad que tiene el idFacultad
del parámetro de entrada.

CREATE PROCEDURE InvByIdFac (IN IdFac INT)
BEGIN

select *
from Investigadores
where Investigadores.IdFacultad = IdFac;

END;


26. Crear un procedimiento al que le pasemos el DNI y nos muestre el nombre y
apellidos del investigador.
CREATE PROCEDURE InvByDNI (IN p_dni VARCHAR(10))
BEGIN
select *
from Investigadores
where dni = p_dni;

END


27. Crear un procedimiento en el que le pasemos como parámetro de entrada
el idEquipo y nos muestre los investigadores que lo han reservado
CREATE PROCEDURE RsvInvByEq(IN p_idEq VARCHAR(4))
begin 
	select v_reservas.IdInvestigador, 
    	   v_reservas.NomInvestigador,
           v_reservas.ApellInvestigador
    from v_reservas
    where v_reservas.IdEquipo = p_idEq;

END


28. Crear un procedimiento que pida un parámetro de entrada de tipo texto y
nos muestre los datos del investigador que contenga el texto del parámetro
de entrada en el nombre o en el apellido.


CREATE PROCEDURE InvByText(IN p_text VARCHAR(100))
BEGIN

select *
from Investigadores
where Investigadores.NomInvestigador like concat('%',p_text,'%')
or Investigadores.ApellInvestigador like concat('%',p_text,'%');


END

29. Crear un procedimiento que traslade a un investigador a otra facultad.
Como parámetro de entrada se pasará el id del investigador y la nueva
facultad a la que irá.

CREATE PROCEDURE traslInvFac(IN idInv INT, IN IdFac INT)
BEGIN

update Investigadores
set Investigadores.IdFacultad = idFac
where Investigadores.IdInvestigador = idInv;

END


30. Crear un procedimiento que cree una nueva facultad. Recibirá de entrada el
nombre de la facultad.
CREATE PROCEDURE AddFacultad(IN NombreFac VARCHAR(50))
BEGIN

declare idFac int;
select  max(idFacultad)+1 into idFac
from Facultad;

insert into Facultad
(idFacultad, NomFacultad)
VALUES
(idFac,NombreFac);


END


*/