-- 0.- Creamos la Base de Datos
CREATE DATABASE Vuelos
GO
USE Vuelos
GO

-- 2.- Creamos la tabla AVIONES
CREATE TABLE AVIONES (
	TIPO varchar(3),
	CAPACIDAD int,
	LONGITUD float,
	ENVERGADURA float,
	VELOCIDAD float,
	CONSTRAINT PK_TIPO PRIMARY KEY (TIPO)
)

-- 1.- Creamos la tabla VUELOS
CREATE TABLE VUELOS (
	NUM_VUELO varchar(10) PRIMARY KEY,
	ORIGEN varchar(20) NOT NULL,
	DESTINO varchar(20) NOT NULL,
	HORA_SALIDA time,
	TIPO_AVION varchar(3) DEFAULT '72S'
						  CHECK (TIPO_AVION IN ('320','73S','72S','737','D9S','747')),
	CONSTRAINT FK_AVIONES_VUELOS FOREIGN KEY (TIPO_AVION) REFERENCES AVIONES(TIPO)
)

-- 2.- Creamos la tabla RESERVAS
CREATE TABLE RESERVAS (
	NUM_VUELO varchar(10),
	FECHA_SALIDA date,
	PLAZAS_LIBRES int,
	CONSTRAINT PK_NUM_VUELO PRIMARY KEY (NUM_VUELO, FECHA_SALIDA),
	CONSTRAINT FK_VUELOS_RESERVAS FOREIGN KEY (NUM_VUELO) REFERENCES VUELOS(NUM_VUELO)
)

-- 3.- Insertamos los datos en la tabla AVIONES
INSERT INTO AVIONES VALUES ('D9S','110','38.3','28.5','815')
INSERT INTO AVIONES VALUES ('320','187','42.15','32.6','853')
INSERT INTO AVIONES VALUES ('72S','160','36.2','25.2','820')
INSERT INTO AVIONES VALUES ('73S','185','44.1','30.35','815')
INSERT INTO AVIONES VALUES ('737','172','38','90.29','793')

-- 3.- Insertamos los datos en la tabla VUELOS
INSERT INTO VUELOS VALUES ('IB600','Madrid','Londres','10:30:00','320')
INSERT INTO VUELOS VALUES ('BA467','Madrid','Londres','20:40:00','73S')
INSERT INTO VUELOS VALUES ('IB0640','Madrid','Barcelona','06:45:00','320')
INSERT INTO VUELOS VALUES ('IB3742','Madrid','Barcelona','09:15:00',DEFAULT)
INSERT INTO VUELOS VALUES ('LH1349','Copenhague','Francfort','10:20:00','320')
INSERT INTO VUELOS VALUES ('AF577','Bilbao','París','10:10:00','737')
INSERT INTO VUELOS VALUES ('IB3709','Dublín','Barcelona','14:35:00','D9S')
INSERT INTO VUELOS VALUES ('IB778','Barcelona','Roma','09:45:00',DEFAULT)
INSERT INTO VUELOS VALUES ('IB721','Barcelona','Sevilla','16:40:00',DEFAULT)
INSERT INTO VUELOS VALUES ('IB023','Madrid','Tenerife','21:20:00',DEFAULT)
INSERT INTO VUELOS VALUES ('IB368','Málaga','Barcelona','22:25:00','D9S')
INSERT INTO VUELOS VALUES ('IB610','Málaga','Londres','15:05:00','73S')
INSERT INTO VUELOS VALUES ('IB510','Sevilla','Madrid','07:45:00',DEFAULT)
INSERT INTO VUELOS VALUES ('IB318','Sevilla','Madrid','10:45:00',DEFAULT)
INSERT INTO VUELOS VALUES ('IB327','Madrid','Sevilla','18:05:00',DEFAULT)

-- 3.- Insertamos datos en la tabla RESERVAS
INSERT INTO RESERVAS VALUES ('IB600','20/02/2005','46')
INSERT INTO RESERVAS VALUES ('IB600','21/02/2005','80')
INSERT INTO RESERVAS VALUES ('IB600','22/02/2005','91')
INSERT INTO RESERVAS VALUES ('BA467','20/02/2005','32')
INSERT INTO RESERVAS VALUES ('BA467','21/02/2005','89')
INSERT INTO RESERVAS VALUES ('BA467','22/02/2005','79')
INSERT INTO RESERVAS VALUES ('IB0640','20/02/2005','15')
INSERT INTO RESERVAS VALUES ('IB0640','21/02/2005','21')
INSERT INTO RESERVAS VALUES ('IB0640','22/02/2005','39')
INSERT INTO RESERVAS VALUES ('IB3709','20/02/2005','60')
INSERT INTO RESERVAS VALUES ('IB3709','21/02/2005','72')
INSERT INTO RESERVAS VALUES ('IB3709','22/02/2005','85')
INSERT INTO RESERVAS VALUES ('IB510','20/02/2005','19')
INSERT INTO RESERVAS VALUES ('IB510','21/02/2005','31')
INSERT INTO RESERVAS VALUES ('IB510','22/02/2005','40')

-- 4.- Obtener el tipo de avión, capacidad, longitud y destino de los
-- que van a Barcelona ordenador por la capacidad del avión.
SELECT a.TIPO, a.CAPACIDAD, a.LONGITUD, v.DESTINO
FROM AVIONES a JOIN VUELOS v
ON a.TIPO = v.TIPO_AVION
WHERE v.DESTINO = 'Barcelona'
ORDER BY CAPACIDAD

-- 5.- Crear el índice denominado IXVUELOS para las columnas origen y destino ascendente.
CREATE INDEX IXVUELOS ON VUELOS(origen ASC, destino ASC)

-- 6.- Hallar cuantos vuelos hay en cada destino (indicando la ciudad)
-- y la hora del vuelo más temprano, sin considerar orígenes de Málaga
-- ni destinos a Tenerife. Sólo deben aparecer las ciudades que tienen más de un vuelo
SELECT DESTINO AS Ciudad, COUNT(DESTINO) AS Vuelos, MIN(HORA_SALIDA) AS Vuelo_Mas_Pronto
FROM VUELOS WHERE ORIGEN <> 'Malaga' AND DESTINO <> 'Tenerife'
GROUP BY DESTINO HAVING COUNT(*) > 1

-- 7.- Insertar en una tabla denominada aviones_grandes los aviones que tengan
-- una capacidad mayor o igual que 172 plazas, ordenados por la longitud del avión.
-- aviones_grandes tiene los mismos atributos que aviones excepto el atributo
-- denominado velocidad_crucero que no lo tiene
SELECT TIPO, CAPACIDAD, LONGITUD, ENVERGADURA INTO aviones_grandes FROM AVIONES
WHERE CAPACIDAD >= 172 ORDER BY LONGITUD

-- 8.- Reducir la capacidad de los aviones un 10%.+
UPDATE AVIONES SET CAPACIDAD = CAPACIDAD - (CAPACIDAD * 10) / 100

-- 9.- Crear la vista vista_vuelos con las columnas v_origen, v_destino, v_fecha_salida,
-- v_hora_salida,v_plazas_libres.
CREATE VIEW vista_vuelos (v_origen, v_destino, v_fecha_salida, v_hora_salida, v_plazas_libres) AS
SELECT origen, destino, fecha_salida, hora_salida, plazas_libres
FROM VUELOS v JOIN RESERVAS r ON v.NUM_VUELO = r.NUM_VUELO

SELECT * FROM vista_vuelos

-- 10.- Dar el permiso select y update sobre plazas_libres al grupo operador_reservas
-- sobre la tabla reservas. Previamente crear el grupo, dos usuarios usuario1 y
-- usuario2 y hacerlos miembros del grupo. Comprobar
create login Vuelos with password = 'Vuelos'
create login Vuelos2 with password = 'Vuelos2'
create role operador_reservas
create user usuario1 from login Vuelos
create user usuario2 from login Vuelos2
exec sp_addrolemember 'operador_reservas',usuario1
exec sp_addrolemember 'operador_reservas',usuario2

GRANT SELECT, UPDATE on reservas(plazas_libres) to operador_reservas

execute as user='usuario1'
	UPDATE RESERVAS SET PLAZAS_LIBRES = PLAZAS_LIBRES - 1
	SELECT PLAZAS_LIBRES FROM RESERVAS
revert

-- 11.- Quitar el permiso update anterior. Comprobar
REVOKE UPDATE on reservas(plazas_libres) to operador_reservas

execute as user='usuario1'
	UPDATE RESERVAS SET PLAZAS_LIBRES = PLAZAS_LIBRES - 1
revert

-- 12.- Modificar el tipo de avión del vuelo Málaga-Londres de las 15:05 poniendo D9S.
UPDATE VUELOS SET TIPO_AVION = 'D9S' WHERE ORIGEN = 'Málaga' AND DESTINO = 'Londres'

-- 13.- Borrar todas las reservas que tengan menos de 50 plazas libres.
DELETE FROM RESERVAS WHERE PLAZAS_LIBRES < 50

-- 14.- Indicar tipo de avión y capacidad de aquellos modelos que han sido asignados
-- a todos los orígenes posibles

-- 15.- ¿Cuántas plazas libres existen en los vuelos de los próximos 3 meses?
SELECT PLAZAS_LIBRES FROM RESERVAS
WHERE DATEDIFF(MONTH,GETDATE(),FECHA_SALIDA) BETWEEN 0 AND 3

-- 16.- Actualizar la capacidad de los aviones sumándole 6 plazas a aquellos
-- cuya envergadura sea mayor de 30m.
UPDATE AVIONES SET CAPACIDAD = CAPACIDAD + 6 WHERE ENVERGADURA > 30

-- 17.- Recuperar las reservas cuyo número de plazas libres es mayor que la
-- media para ese mismo vuelo.
SELECT * FROM RESERVAS WHERE PLAZAS_LIBRES IN (SELECT AVG(PLAZAS_LIBRES) FROM RESERVAS)

-- 18.- ¿Qué vuelos de Iberia tienen en total más de 150 plazas libres?
SELECT NUM_VUELO FROM RESERVAS WHERE PLAZAS_LIBRES > 150 AND SUBSTRING(NUM_VUELO,1,2) = 'IB'

-- 19.- Modificar la tabla de reservas y añadir las columnas libres_primera
-- y libres_turista de tipo smallint.
ALTER TABLE RESERVAS ADD libres_primera smallint, libres_turista smallint

-- 20.- Recuperar el nº de vuelo y la hora de salida de todos los vuelos que
-- hacen el trayecto Madrid-Londres.
SELECT NUM_VUELO, HORA_SALIDA FROM VUELOS WHERE ORIGEN = 'Madrid' AND DESTINO = 'Londres'

-- 21.- Cargar en una nueva tabla todas las ciudades sean de origen o de destino.
SELECT ORIGEN, DESTINO INTO Ciudades FROM VUELOS

-- 22.- Modificar el tipo de datos del atributo hora_salida cambiándolo
-- de datetime a smalldatetime.
ALTER TABLE VUELOS ALTER COLUMN HORA_SALIDA SMALLDATETIME

-- 23.- Seleccionar el total de plazas libres por cada num_vuelo
-- de aquellos vuelos de Iberia.
SELECT PLAZAS_LIBRES, NUM_VUELO FROM RESERVAS WHERE SUBSTRING(NUM_VUELO,1,2) = 'IB'

-- 24.- ¿Qué vuelos tienen su salida hacia Madrid las dos próximas horas?
SELECT HORA_SALIDA FROM VUELOS
WHERE DESTINO = 'Madrid' AND HORA_SALIDA BETWEEN GETDATE() AND DATEADD(HOUR,2,GETDATE())

-- 25.- Obtener los tipos de aviones y sus capacidades para los que tengan
-- menos de 30 plazas libres
SELECT TIPO, CAPACIDAD FROM AVIONES a JOIN VUELOS v ON a.TIPO = v.TIPO_AVION
JOIN RESERVAS r ON v.NUM_VUELO = r.NUM_VUELO WHERE PLAZAS_LIBRES < 30

-- 26.- Se quieren recuperar los aviones que hacen su recorrido entre las
-- 16 horas y 1/4 hora después como término medio.
SELECT * FROM AVIONES a JOIN VUELOS v ON a.TIPO = v.TIPO_AVION
WHERE HORA_SALIDA BETWEEN '16:00:00' AND '17:15:00'

-- 27.- Obtener el número de vuelo, origen y destino para aquellos vuelos cuyo horario
-- de salida sea mayor que el del último vuelo con destino a Sevilla, ordenado por la ciudad de origen.
SELECT DISTINCT NUM_VUELO, ORIGEN, DESTINO FROM AVIONES, VUELOS
WHERE HORA_SALIDA > (SELECT MAX(HORA_SALIDA) FROM VUELOS WHERE DESTINO = 'Sevilla')
ORDER BY ORIGEN

-- 28.- Recuperar las características de los aviones que no pasan por Barcelona. Usar not exists.
SELECT * FROM AVIONES a WHERE NOT EXISTS
(SELECT DESTINO FROM VUELOS WHERE a.TIPO=TIPO_AVION AND DESTINO = 'Barcelona')

-- 29.- Seleccionar todos los datos de aquellos vuelos cuyo tipo contenga en su denominación
-- el conjunto de caracteres '9S'.
SELECT * FROM VUELOS WHERE TIPO_AVION LIKE '%9S'

-- 30.- ¿Cuántas plazas libres hay en los vuelos Madrid-Londres para el 22/02/2003?
SELECT PLAZAS_LIBRES FROM RESERVAS r JOIN VUELOS v ON r.NUM_VUELO = v.NUM_VUELO
WHERE ORIGEN = 'Madrid' AND DESTINO = 'Londres' AND FECHA_SALIDA = '22/02/2003'

-- 31.- Obtener el tipo de avión, capacidad, longitud y destino de los que van a Barcelona,
-- ordenados por la capacidad del avión.
SELECT DISTINCT TIPO, CAPACIDAD, LONGITUD, DESTINO FROM AVIONES, VUELOS
WHERE DESTINO = 'Barcelona' ORDER BY CAPACIDAD

-- 32.- Recuperar los aviones cuya longitud sea mayor que la envergadura de todos ellos.
SELECT * FROM AVIONES WHERE LONGITUD > (SELECT SUM(ENVERGADURA) FROM AVIONES)

-- 33.- Recuperar todos los vuelos cuyo origen está en Madrid, Barcelona o Sevilla.
SELECT * FROM VUELOS WHERE ORIGEN = 'Madrid' OR ORIGEN = 'Barcelona' OR ORIGEN = 'Sevilla'

-- 34.- Obtener los valores extremos de la columna velocidad_crucero.
SELECT MIN(VELOCIDAD) AS Velocidad_Min, MAX(VELOCIDAD)AS Velocidad_Max FROM AVIONES

-- 35.- Seleccionar el número de vuelo, origen y destino de aquellos vuelos con origen en
-- Madrid para los que quedan plazas libres. Usar exists y otra versión.


-- 36.- ¿Cuántos destinos distintos existen?
SELECT COUNT(DISTINCT DESTINO) AS DESTINOS_TOTALES FROM VUELOS

-- 37.- Recuperar los aviones cuya capacidad sea menor que el doble de alguno de los promedios de plazas libres por día.
SELECT * FROM AVIONES WHERE CAPACIDAD < ANY
(SELECT 2 * AVG(PLAZAS_LIBRES) FROM RESERVAS GROUP BY FECHA_SALIDA)

-- 38.- ¿Cuántas plazas libres existen en los vuelos Madrid-Londres de las 20:40h del 22/02/2003? Utilizar subconsultas.


-- 39.- Consultar la hora de salida más temprana de los vuelos que no van a Barcelona, clasificados por origen.
SELECT MIN(HORA_SALIDA) FROM VUELOS WHERE DESTINO <> 'Barcelona' GROUP BY ORIGEN

-- 40.- Listar la hora de salida más tardía y el número de vuelos clasificados por origen,
-- siempre que su hora de salida sea anterior a las 4 de la tarde.
SELECT MAX(HORA_SALIDA), COUNT(*) FROM VUELOS WHERE HORA_SALIDA < '16:00:00' GROUP BY ORIGEN

-- 41.- Crear el índice denominado ixreservas para las columnas num_vuelo y fecha_salida ascendente
-- y que no admita duplicados.
CREATE UNIQUE INDEX ixreservas ON RESERVAS (NUM_VUELO, FECHA_SALIDA ASC)

-- 42.- Denegar a Usuario1 el permiso update sobre plazas libres. Comprobar
DENY UPDATE on reservas(plazas_libres) to usuario1

execute as user='usuario1'
	UPDATE RESERVAS SET PLAZAS_LIBRES = PLAZAS_LIBRES - 1
revert