SQL Ejercicio

MISSION (Codigo, tipo, sistema, lugar, peligrosidad, exito)
REBELDE (Nombre, sistema, lugar)
HACE (cod_mision, nom_rebelde)
      cod_mision es FK a Codigo de MISSION
      nom_rebelde es FK a Nombre de REBELDE
JEDI (Nombre_jedi, nivel_fuerza)
      Nombre es FK a Nombre de REBELDE
PILOTO (Nombre_piloto, nave_espacial)
      Nombre es FK a Nombre de REBELDE

1) Mostrar todos los datos de las misiones con una peligrosidad superior a la media:

SELECT * FROM Mission WHERE peligrosidad > 
       (SELECT AVG(peligrosidad) FROM Mission);

2) Mostrar el nombre del jedi con un nivel de fuerza superior al resto:

SELECT nom_pilot
FROM Pilot 
WHERE nau_espacial=
		(
		SELECT t.nau_espacial
		FROM (
			SELECT TOP 1 COUNT(*) AS num_pilotos, nau_espacial
			FROM Pilot 
			GROUP BY nau_espacial
			ORDER BY num_pilotos DESC
			) AS t
	);

3) Mostrar los nombres de los piltotos que pilotan la nave espacial que más pilotos pilotan:

4) Se quiere saber para cada sistema, cuantos jedais y cuantos pilotos hay:

SELECT *
FROM
	(SELECT r.sistema, COUNT(j.Nombre_jedi) AS Num_Jedi
	FROM Rebelde r 
	JOIN Jedi j ON r.Nombre = j.Nombre_jedi 
	GROUP BY r.sistema) AS Una
FULL OUTER JOIN
	(SELECT r.sistema, COUNT(p.Nombre_piloto) AS Num_Pilot
	FROM Rebelde r 
	JOIN Piloto p ON r.Nombre = p.Nombre_pilot 
	GROUP BY r.sistema) AS Dos
ON Una.sistema=Dos.sistema

5) Mostrar todos los datos del jedi que más misiones con éxito haya acabado:

SELECT *
FROM Jedi j
JOIN Rebelde r ON j.Nombre_jedi=r.Nombre
WHERE j.Nombre_jedi=
		(
			SELECT t.Nombre_jedi
			FROM (
				SELECT TOP 1 COUNT(j.Nombre_jedi) AS Misiones_Exito, j.Nombre_jedi
				FROM Jedi j
				JOIN FA ON j.Nombre_jedi=FA.Nombre_rebel
				JOIN Mission m ON FA.codi_mision=m.Codigo
				WHERE m.exito = 'True'
				GROUP BY j.Nombre_jedi
				ORDER BY Misiones_Exito DESC
			) AS t
		);

6) Mostrar para cada misión pendiente de realitzarse (exito = null) que rebeldes estan en el mismo sistema y lugar que la misión:

SELECT *
FROM Mission m
JOIN FA ON m.Codigo=FA.cod_mision
JOIN Rebelde r ON FA.nom_rebelde=r.Nombre
WHERE m.exito IS NULL 
      AND m.sistema=r.sistema 
      AND m.lugar=r.lugar

7) Mostrar todos los datos de las misiones con una peligrosidad inferior a la media, que hayan estado realizadas por mas de dos rebeldes:

SELECT *
FROM Mission
JOIN 
		(
		SELECT mi.codigo
		FROM
			(
				SELECT m.codigo, COUNT(FA.nombre_rebelde) numero_rebelde
				FROM Mission m
				JOIN FA ON m.codigo=FA.codigo_mision
				WHERE m.peligrosidad < (SELECT AVG(m.peligrosidad) FROM Mission m)
				GROUP BY m.codigo
			) mi
		WHERE mi.numero_rebelde>2
		) codigo_mp
ON Mission.codigo=codigo_mp.codigo

8) Hay rebeldes que no són ni pilotos ni jedis, però que tienen una gran importancia para la resistencia. Hay que mostrar todos los datos de estos rebeldes, así como las de los rebeldes que esten en su misma localización (sistema i lugar)

(
	SELECT *, 'Rebelde sin rol' AS tipo
	FROM Rebelde r 
	WHERE Not r.nombre In (SELECT j.nombre_jedi FROM Jedi j) 
        AND Not r.nombre In (SELECT p.nombre_piloto FROM piloto p)
)
UNION 
(
	SELECT r.nombre, r.sistema, r.lugar, 'Jedi o Piloto' AS tipo
	FROM Rebelde r 
	JOIN (
		SELECT *
		FROM Rebelde r 
		WHERE Not r.nombre In (SELECT j.nombre_jedi FROM Jedi j) 
			AND Not r.nombre In (SELECT p.nombre_piloto FROM piloto p)
		) sin_rol
	ON r.lugar=sin_rol.lugar AND r.sistema=sin_rol.sistema
)
ORDER BY tipo DESC

9) El nivel de fuerza de un jedi aumenta en función de las misiones que realiza con éxito. Para cada misión con peligrosidad 10 realizada con éxito donde haya participado, el nivel de fuerza del jedi aumenta en un punt. Actualiza la base de datos con esta información:

UPDATE Jedi 
SET nivell_forsa = nivell_forsa + 1 
WHERE nom_jedi IN
	(
	SELECT j.nom_jedi 
	FROM Mision m 
	JOIN FA f ON m.codi=f.codi_missio AND m.te_exit= 'True' AND perillositat=10
	JOIN Jedi j ON f.nom_rebel=j.nom_jedi
	)

10) El lugar más peligroso del universo para un rebelde és aquel en que se han realizado más misiones sin éxito. Hay que hacer una consulta para mostrar cual és este lugar (sistema y lugar).

SELECT TOP 1 COUNT(*) AS num_misiones_fallidas, m.sistema, m.lloc 
	FROM Missio m 
	WHERE m.te_exit= 'False'
	GROUP BY m.sistema, m.lloc
ORDER BY num_misiones_fallidas DESC

11) Crear un TRIGGER que en caso de que se cree una nueva misión se asigne los rebeldes que no estan en ninguna misión en este momento.

- Realizamos el insert
-- INSERT INTO Missio (codi, tipus, sistema, lloc, perillositat)
-- VALUES ('14','GKU','Alpha','Satelite',5);

-- Función que devuelve los reveldes que no están asignados a ninguna misión
CREATE FUNCTION dbo.reveldes_sin_mision()
	RETURNS table
AS
RETURN (SELECT r.nom
		FROM Rebel r 
		WHERE r.nom NOT IN
			-- Rebels que tenen missió asignada
			(
			SELECT r.nom
			FROM FA f
			JOIN Rebel r ON f.nom_rebel=r.nom
			GROUP BY r.nom
			)
		)

-- ######### TRIGGER #########################################
---SELECT * FROM reveldes_sin_mision()
CREATE TRIGGER dbo.asignar_rebels
	ON dbo.Missio
AFTER INSERT
AS
BEGIN
	SET NOCOUNT ON;
	-- Recibir codigo de la misión nueva
	DECLARE @Codi_Misio VARCHAR(50);
	SELECT @Codi_Misio = INSERTED.codi
	FROM INSERTED
	
	-- Rebeldes que no tienen misión asignada
	--DECLARE @ReveldesLibres TABLE (nom varchar(50));
	@ReveldesLibres = SELECT COUNT(*) FROM reveldes_sin_mision();

	-- BUCLE para todos los reveldes
		DECLARE @Puntero INT = 0;
		WHILE (@Puntero < @ReveldesLibres )
		BEGIN
			INSERT INTO FA (codi_missio, nom_rebel)
			VALUES ( @Codi_Misio, XXXXXXXXXXXXXXX)
			SET @Puntero = @Puntero + 1;
		END
END
GO